Articles & Content

StackOverflow

StackOverflow is a collaborative programming Q&A community venture

Note: The content of this blog is the opinion and thoughts of the blogger and does not necessarily represent the opinions of IDUG.

 

 
DB2 DataSource properties for KeyStore are nowhere to be found

I'm using db2 datasource with Hikari and I'm trying to set my keystore location and password using either datasource properties with addDataSourceProperty or using query params in the url with setJdbcUrl.

I'm trying to do it like that instead of setting the keystore location and password with system properties using setProperty which is currently working.

However, when I try to use the properties db2.jcc.DB2BaseDataSource.sslKeyStoreLocation, db2.jcc.DB2BaseDataSource.sslKeyStorePassword, and db2.jcc.DB2BaseDataSource.sslKeyStoreType, the DB2BaseDataSource class can't find those fields.

Please help if you can, I've been banging my head on this because apparently it's how I'm supposed to do it.

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/java/src/tpc/imjcc_r0052075.html#imjcc_r0052075__tblcfgpr2

https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_r0021822.html

IBM DB2 on Cloud - Missing "Result Set" tab when running a SQL statement

Wondering if you can help me. I am using IBM DB2 on Cloud, and running an SQL statement, for instance creating a table.Unfortunately, the "Result Set" tab is not displayed in the SQL Editor, only the Log tab.Without the Result Set tab I am not able to see the outcome when running the sql statement. Any idea how I can make the tab "Result Set" being displayed?Much appreciate itRomeo

Make a DB2 Schema public so that all tables are readable to PUBLIC

I have a shared schema that any user (PUBLIC) should be able to read. However, I can only find a way to set each table PUBLIC. Is there a way to set the schema to have all of their tables SELECT PUBLIC?

While i am executing the DB2 Java class and i am getting db2 error

While i am executing the DB2 Java class and i am getting the below error.

com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-4742, SQLSTATE=560D5, SQLERRMC=6, DRIVER=4.24.92

Thanks,Rasheed SK

LISTAGG in WHERE Clause

I need a query something like

select id,name from samplewhere (:test IS NULL OR name IN (:test)))

Is this possible? I don't want to use inner queries.

SQL Developer "disconnected from the rest of the join graph"

I have the following SQL:

select from pluspbillline left outer join workorder on workorder.siteid=pluspbillline.siteid and workorder.wonum = pluspbillline.refwo and workorder.orgid = pluspbillline.orgidleft outer join ticket on ticket.ticketid = pluspbillline.ticketid and ticket.class=pluspbillline.ticketclassleft outer join pluspsalesorder on pluspsalesorder.salesordernum=pluspbillline.salesordernum and pluspsalesorder.siteid=pluspbillline.siteid

In Oracle SQL Developer 4.0.0.13 (connected to a DB2 database), I get a squiggly line underneath the following italics: "from pluspbillline" and "left outer join workorder".

The warning says "pluspbillline is disconnected from the rest of the join graph". What does this mean?

Command line switches for CWBTF.EXE aka Data Transfer From IBM i..?

I would like to add an Edit action to the right-click context menu for IBM *.DTF files in my Windows Registry.

What are the command lines switches for CWBTF.EXE..? Is there a switch for "open DTF file for editing" (but do not run it)..?

I've searched Google, StackOverflow, and the the IBM i 7.3 documentation for any mention of the command line switches for CWBTF.EXE aka "Data Transfer From IBM i", but I've found none.

The shortcut on my Windows Start Menu for "Data Transfer From IBM i" has the /t switch, but I cannot find any information on this.

I did find the command-line transfer utility RTOPCB.EXE, which does have some /? help text, but the options did not seem to relate much to the full-GUI version CWBTF.EXE.

Result Set is closed error when referencing table via JPA (Websphere and DB2)

I'm using the IBM implentation of Open JPA on WebSphere 7 and I'm having an issue when I'm trying to reference an object that is @ManyToOne and keep getting the following error from DB2:

com.ibm.db2.jcc.b.SqlException: [jcc][t4][10120][10898][3.50.152] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null

I'm pulling my hair out as to why this doesn't work and hope that somebody can help.

Here is a simplified view the database schemas:

Table Report

record_id - integer - (primary key - generated by DB2)

agency - integer not null (foreign key to Dropdown table)

Table Dropdown

record_id - integer - (primary key - generated by DB2)

Here is the JPA entity for the Report which references the agency

@ManyToOne(fetch=FetchType.EAGER)@JoinColumn(name="AGENCY")private Dropdown agency;

Here is the code where I'm running a named query to get the data and then just iterating over the result set to print out the report id and the agnency. Whenever report.getAgency() is called, I get the "result set is closed" error from DB2:

@SuppressWarnings("unchecked")public List getOpenIncidentsForUser(String aceId) throws Exception{ List results = null; EntityManager em = getEntityManager(); try { Query query = em.createNamedQuery("getOpenIncidentsForUser"); query.setParameter(1, aceId); results = (List) query.getResultList(); Iterator it = results.iterator(); while(it.hasNext()) { Report report = it.next(); System.out.println("Report [" + report.getRecordId() + "] Agency: [" + report.getAgency() + "]"); } } catch (Exception e) { log.fatal("Fatal error getting incidents for user", e); throw e; } finally { em.close(); } return (List) results;}

if I don't ever refer to the getAgency method, I can print out anything else about the report with no problems. It only seems to be with the reference to the 2nd table. Any ideas?

sufficient page size does not exist - DB2 insert

I am having a DB2 query(simple insert statement) which is trying to insert some 27 columns. Out of those columns 1 is Clob and the issue is there. For my Clob column, sometimes the value might even contain 28K characters. And in such extreme cases, I am getting the below error,

 A system temporary table space with sufficient page size does not exist .. SQLCODE=-1585, SQLSTATE=54048, DRIVER=3.64.82

As I googled and gone through some pages, there is an entity called System Temporary Table Space which will be used by the DB when executing the query.(I am not sure, but few pages says that it will be used only for sorting and joining, but i don't have either in my query).

After going through few suggestions I created a System Temporary Table Space with the page size of 32K, using the below query,

CREATE SYSTEM TEMPORARY TABLESPACE STB PAGESIZE 32K MANAGED BY SYSTEM USING ( 'C:\DB2\NODE0005') BUFFERPOOL BP32K

Still my issue continues. What would be the proper way to have my query executed. I am trying to understand the importance of System Temporary Table Space, Page Size,etc.. But any help that could fix this issue for now would be greatly appreciated.

SQL first date for the first occurrence of a value

I have a table with clients and the history of their rating (low, medium, high) which gets assessed quarterly.

Example table:

client_ID rating eff_from111 high 30.09.2018111 high 30.06.2018111 medium 31.03.2018111 high 31.12.2017111 high 30.09.2017111 low 30.06.2017222 medium 30.09.2018222 high 30.06.2018222 high 31.03.2018222 low 31.12.2017222 low 30.09.2017222 medium 30.06.2017

I would like to get the minimum eff_from date for the latest rating. From the table above, that would be 30.06.2018 for client 111 and 30.09.2018 for client 222.

The trick is that the rating can change for example from high to medium and back to high, so grouping by client and rating and taking the minimum eff_from date doesn't work - it would result in 30.09.2017 for client 111.

The environment is a DB2 database and the eff_from field is formatted as date.

Any ideas?