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.

 

 
I need help to reporting from db2 [on hold]

I have a table that include all data about a ticket history like assigned, inprog, datareq, who is owner etc.. I want to report information of owner group changes,the first owner group and the last owner group. how can I do that ? (ı know that it is a specific situation :( )

Zero not returning in DB2 when select query yields no result

I am running the below select query in SSIS which is using DB2 connection.

SELECT (SELECT COUNT(*) FROM tbl.Test) AS NoOfDestFROM tbl.IndexFETCH FIRST 1 ROW ONLY;

When this query yields no result, I am getting the below error in SSIS.

single row result set is specified but no rows were returned.

So I am trying to return 0 in DB2 instead of 'blank/no result'. I have tried the below.

SELECT (COALESCE((SELECT COUNT(*) FROM tbl.Test), 0)) AS NoOfDestFROM tbl.IndexFETCH FIRST 1 ROW ONLY;SELECT (NVL((SELECT COUNT(*) FROM tbl.Test), 0)) AS NoOfDestFROM tbl.IndexFETCH FIRST 1 ROW ONLY;

But everything returning no result.

How can I return 0 in DB2 ?

Can Nifi load data from DB2 to Cassandra?

I am exploring options to load data from DB2 to Cassandra using open source tools. I read through some of the blogs and articles and found Nifi is one of the recommended tool. Need some inputs on whether Nifi could perform this job or any other better tool? Any other suggestions to load data? Like any native connector to pull data from DB2?? Many thanks in advance for help.

Thanks,Arun

Cannot set CURRENT OPTIMIZATION HINT special register via db2dsdriver.cfg <specialregisters> section

I was trying to provide a default value for the CURRENT OPTIMIZATION HINT special register for my DB2 client application.

I tried db2dsdriver.cfg file and it's section and JDBC specialRegisters Uri parameter. Both lead to the same result:

  1. When I set the value without quotes like so:

db2dsdriver.cfg:

   

or with JDBC Uri:

jdbc:db2://:/:retrieveMessagesFromServerOnGetMessage=true;emulateParameterMetaDataForZCalls=1;specialRegisters=CURRENT OPTIMIZATION HINT=HELLO1;

I get an exception saying I have a problem with the SET instruction:

Message: ERROR [42721] [IBM][DB2] SQL0969N There is no message text corresponding to SQL error "-725" in the message file on this workstation. The error was returned from module "DSNLXENV" with original tokens "CURRENT QUERY OPTIMIZATION DALLASA". SQLSTATE=42721

Here is the exempt from the java trace:

...[jcc][t4] [jcc][t4] SEND BUFFER: SQLSTT (ASCII) (EBCDIC)[jcc][t4] 0000 0036D04300010030 2414000000002653 .6.C...0$.....&S ..}.............[jcc][t4] 0010 4554204355525245 4E54204F5054494D ET CURRENT OPTIM ........+..|&..([jcc][t4] 0020 495A4154494F4E20 48494E54203D2048 IZATION HINT = H .!...|+...+.....[jcc][t4] 0030 454C4C4F31FF ELLO1. .<<|.. [jcc][t4] ...[jcc][Thread:Worker-103][SQLException@d1aed064] java.sql.SQLException[jcc][Thread:Worker-103][SQLException@d1aed064][Sqlca@a74669be] DB2 SQLCA from server[jcc][Thread:Worker-103][SQLException@d1aed064][Sqlca@a74669be] SqlCode = -725[jcc][Thread:Worker-103][SQLException@d1aed064][Sqlca@a74669be] SqlErrd = { 0, 0, 0, -1, 0, 0 }[jcc][Thread:Worker-103][SQLException@d1aed064][Sqlca@a74669be] SqlErrmc = CURRENT QUERY OPTIMIZATION;DALLASA[jcc][Thread:Worker-103][SQLException@d1aed064][Sqlca@a74669be] SqlErrmcTokens = { CURRENT QUERY OPTIMIZATION, DALLASA }[jcc][Thread:Worker-103][SQLException@d1aed064][Sqlca@a74669be] SqlErrp = DSNLXENV[jcc][Thread:Worker-103][SQLException@d1aed064][Sqlca@a74669be] SqlState = 42721[jcc][Thread:Worker-103][SQLException@d1aed064][Sqlca@a74669be] SqlWarn = [jcc][Thread:Worker-103][SQLException@d1aed064] SQL state = 42721[jcc][Thread:Worker-103][SQLException@d1aed064] Error code = -725[jcc][Thread:Worker-103][SQLException@d1aed064] Tokens = CURRENT QUERY OPTIMIZATION;DALLASA[jcc][Thread:Worker-103][SQLException@d1aed064] Stack trace followscom.ibm.db2.jcc.am.SqlSyntaxErrorException: CURRENT QUERY OPTIMIZATION;DALLASA at com.ibm.db2.jcc.am.kd.a(Unknown Source) at com.ibm.db2.jcc.am.kd.a(Unknown Source) at com.ibm.db2.jcc.am.kd.a(Unknown Source) at com.ibm.db2.jcc.am.jg.c(Unknown Source) at com.ibm.db2.jcc.am.jg.a(Unknown Source) at com.ibm.db2.jcc.t4.bb.k(Unknown Source) at com.ibm.db2.jcc.t4.bb.g(Unknown Source) at com.ibm.db2.jcc.t4.p.f(Unknown Source) at com.ibm.db2.jcc.t4.b.readSetGenericSQLSetInfo_(Unknown Source) at com.ibm.db2.jcc.am.qg.b(Unknown Source) at com.ibm.db2.jcc.am.sg.b(Unknown Source) at com.ibm.db2.jcc.am.Agent.readPiggybackCommands(Unknown Source) at com.ibm.db2.jcc.am.Agent.beginReadChain(Unknown Source) at com.ibm.db2.jcc.t4.a.beginReadChain(Unknown Source) at com.ibm.db2.jcc.am.Agent.flow(Unknown Source) at com.ibm.db2.jcc.am.fp.a(Unknown Source) at com.ibm.db2.jcc.am.fp.a(Unknown Source) at com.ibm.db2.jcc.am.fp.executeQuery(Unknown Source) at com.ibm.datatools.filter.DependencyService.executeCountQuery(Unknown Source) at com.ibm.datatools.filter.DependencyService.access$3(Unknown Source) at com.ibm.datatools.filter.DependencyService$LoadCountsJob.run(Unknown Source) at org.eclipse.core.internal.jobs.Worker.run(Unknown Source)

  1. When I try the same and put quotes around the value, I get some strange results:

db2dsdriver.cfg:

   

or with JDBC Uri:

jdbc:db2://:/:retrieveMessagesFromServerOnGetMessage=true;emulateParameterMetaDataForZCalls=1;specialRegisters=CURRENT OPTIMIZATION HINT='HELLO1';

The result is that the special registed is set to some faulty value:

SELECT CURRENT OPTIMIZATION HINT FROM SYSIBM.SYSDUMMY1;

returns a string:

"\0\0\0\u00010\u001a\0\0"

Here is my environment info:

C:\Windows\system32>db2levelDB21085I This instance or install (instance name, where applicable: "DB2")uses "64" bits and DB2 code release "SQL10012" with level identifier"0203010E".Informational tokens are "DB2 v10.1.200.238", "s121127", "IP23389", and FixPack "2".Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".C:\Windows\system32>db2set -all[e] DB2PATH=C:\Program Files\IBM\SQLLIB[i] DB2_SELECTIVITY=ALL[i] DB2INSTPROF=C:\ProgramData\IBM\DB2\DB2COPY1[i] DB2CODEPAGE=1252[g] DB2_EXTSECURITY=NO[g] DB2_COMMON_APP_DATA_PATH=C:\ProgramData[g] DB2PATH=C:\Program Files\IBM\SQLLIB[g] DB2INSTDEF=DB2C:\Windows\system32>db2cli validateIBM DATABASE 2 Interactive CLI Sample Program(C) COPYRIGHT International Business Machines Corp. 1993,1996All Rights ReservedLicensed Materials - Property of IBMUS Government Users Restricted Rights - Use, duplication ordisclosure restricted by GSA ADP Schedule Contract with IBM Corp. --------------------------------------------------------------------------- [ CLI Driver Version : 10.01.0000 ] [ Informational Tokens: "DB2 v10.1.200.238","s121127","IP23389","Fixpack 2" ] [ CLI Driver Type : IBM Data Server Runtime Client ] [ db2diag.log Path : C:\ProgramData\IBM\DB2\DB2COPY1\DB2\db2diag.log ] --------------------------------------------------------------------------- IBM Data Server Client packages on the current workstation : Copyname Version Installed Location --------------------------------------------------------------------------- DB2COPY1[C,D] 10.01.0002 C:\Program Files\IBM\SQLLIB ---------------------------------------------------------------------------db2dsdriver.cfg Schema Validation :Success: The schema validation operation completed successfully.The configuration file C:\ProgramData\IBM\DB2\DB2COPY1\cfg\db2dsdriver.cfg is validThe validation completed.

For DDF DB2 access I am using:

  • IBM Data Studio 4.1.3 which in turn uses JDBC driver db2jcc4.jar version 4.25.1301
  • Custom .NET application using IBM.Data.DB2.dll version 10.1.2.2
How to cast hex data string to a string db2 sql

How would you decode a hex string to get the value in text format by using a select statement?

For example my data in hex is:

4f004e004c005900200046004f00520020004200410043004b002d005500500020004f004e0020004c004500560045004c0020004f004e004500200046004f00520020004300520041004e004500530020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020000000

I want to decode it to get the string value using a select statement.The value of the above is "ONLY FOR BACK-UP ON LEVEL ONE FOR CRANES"

what I have tried is :

 SELECT CAST('4f004e004c005900200046004f00520020004200410043004b002d005500500020004f004e0020004c004500560045004c0020004f004e004500200046004f00520020004300520041004e004500530020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020000000' AS VARCHAR(30000) CCSID 37) from myschema.atable

The above sql returns the exact same hex string and not the decoded text string of "ONLY FOR BACK-UP ON LEVEL ONE FOR CRANES" what I expected.

Is it possible to do this with a cast? If it is what will the syntax be?

My problem that I have is a system stores text data in a blob field and I want to use a select statement to see what the text data is in the blob field.

Db : Db2 on Ibm

Edit:

I have managed to covert the string to the hex value by using :

 select hex(cast('ONLY FOR BACK-UP ON LEVEL ONE FOR CRANES' as varchar(100) ccsid 1208))FROM myschema.atable

This gives me the string in hex :

4F4E4C5920464F52204241434B2D5550204F4E204C4556454C204F4E4520464F52204352414E4553

Now somehow I need to do the inverse and get the value.

Thanks.

Edit

Using the answer from Daniel Lema, I tried using the unhex function but my result that I got was :

|+<ßã|êâ ä.í&|+<áîá<|+áã|êäê +áë

Is this something to do with a CSSID? Or how should I convet the above to a readable string?

This is the table field definition if it will help the field with my data in is GDTXFT a BLOB :

enter image description here

‬‎ <column name> ‪is‬‎ ‪not‬‎ ‪valid‬‎ ‪in‬‎ ‪the‬‎ ‪context‬‎ ‪where‬‎ ‪it‬‎ ‪is‬‎ ‪used‬‎.‪‬‎

I've been at this Create Trigger for a while...

I'm using IBM Data Studio 4.1.3 while making this Trigger. At first I had problems with ending statements with ';' but on the IBM website it says to use 'x' and it works.

My main problem, however, wondering why I get this message:

‬‪‬‎"‪N.ITEMNAME"‬‎ ‪is‬‎ ‪not‬‎ ‪valid‬‎ ‪in‬‎ ‪the‬‎ ‪context‬‎ ‪where‬‎ ‪it‬‎ ‪is‬‎ ‪used‬‎.‪‬‎.‪‬‎ ‪SQLCODE‬‎=‪‬‎-‪206‬‎,‪‬‎ ‪SQLSTATE‬‎=‪42703‬‎,‪‬‎ ‪DRIVER‬‎=‪3‬‎.‪69‬‎.‪56

This also applies to all the others: o.itemid, o.quantity, and n.quantity. I found this out when switching/swapping the names around each other.

The editor is telling me that it has no errors in the statement but when executing, problems arise.

-- CREATE TRIGGER DB2ADMIN.SUPPLIES_IAFTER UPDATE OF QUANTITY ON DB2ADMIN.SUPPLIESREFERENCING NEW TABLE AS n OLD TABLE AS o FOR EACH ROW MODE DB2SQL NOT SECUREDBEGIN ATOMIC INSERT INTO db2admin.tran_log VALUES (USER, CURRENT TIMESTAMP || ' ' || n.itemname || ' ( ' || o.itemid || ' ) from ' || CHAR(o.quantity) || ' to ' || CHAR(n.quantity));END
DB2 - how to call a stored procedure that returns a result set in another user defined table function

I have a db2 stored procedure that takes in some parameters, gets some data from somewhere and then returns a result set through a cursor.

Now I want to write a table function in db2, that will call this stored procedure, read from the result set and return the data in the result set as a table (eventually I want to use this table function in a join).

I would like to know if this is permitted in db2 (we're using DB2 v10.5), i.e. execute a stored procedure in a table function and fetch and read from the result set from the stored procedure. If so, what is the right syntax for calling the stored procedure and reading the result set inside a table function in db2? Thanks!

How to use dynamic AND or OR operator in where clause in DB2?
DECLARE FILTER_DATA CURSOR WITH RETURN FORSELECT C_ID, C_NAME FROM DB2ADMIN.COURSESWHERE C_ID = PARM_ID AND AND C_NAME = PARM_NAME

A procedure contains this cursor and the cursor have AND operator in WHERE clause, but I want to make this operator dynamic based on procedure parameter.

So that the cursor return resultset on the basis of either AND or OR operator.

Thanks in advance!

DB2 sql: How to generate unique ids of a certain length

I'm trying to use python to generate a list of unique ids that can be used as indexes in a table on our DB2 database. My starting input is a list of ids come from a seperate table. I need to take this list of ids and generate a list of other ids (place inside the formlist variable here) These other ids must be unique and must not already exist on the target database table (table name is below shown as FORM_RPT

So far what I have tried is the following:

import ibm_db_dbiimport ibm_dbimport numpy as npimport pandas as pdclass Gen_IDs(): def __init__(self, mycon, opt_ids): """Create an ID Generator object, requires an opt_id list as argument""" self.mycon = mycon self.opt_ids = opt_idsdef gen_form(self): """generates unique form ids based off an option list""" sql = """SELECT * FROM FORM_RPT""" df = pd.read_sql(sql, self.mycon) formlist = list(df["FORM_RPT_ID"]) stack = 0 opt_list = [] while(stack < len(self.opt_ids)): f = np.random.randint(1000, 9999) #if f in df['FORM_RPT_ID'].values: if formlist.count(f) > 0: pass if f in opt_list: pass else: opt_list.append(f) stack += 1 return opt_list

This code is generating just fine, but to my confusion, a small portion of the generated ids are still showing as existing in the target database. The generated ids need to be 4 digits ints.

Here is an example of how it would work:

optionList = [1001, 1002, 1003, 1004, 1005]formlist = [2001, 2002, 2003, 2004, 2005]gm = Gen_Ids(optionList)new_form_list = gm.gen_form()

Currently I'm getting a returned list, but the new list sometimes will have ids that exist in my formList variable.

SQL update from one Table to another based on a ID match IN db2

The Query below is suited in SQL sErver. But in DB2 it does not give results:

Error is SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD FROM. 

Query:

UPDATE Sales_Import SET Sales_Import.AccountNumber = RAN.AccountNumberFROMSales_Import SIINNER JOINRetrieveAccountNumber RANON SI.LeadID = RAN.LeadID

Can someone please clarify the differences b/w DB2 and SQL queries.