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.

 

 
loading data on to ibm db2 cloud database. Authorisation issue

I am trying to load data from my computer (previously downloaded from Chicago Crime database) on to my db2 cloud database (IBM) and keep receiving this error message:

Error for batch element #1: The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Authorization ID: "GNK69870". Operation: "CREATE TABLE". Object: "DB2INST1.CENSUS".. SQLCODE=-551, SQLSTATE=42501, DRIVER=4.26.14Number of occurrences: 1

I am unsure why it is saying that I don't have the access because it is part of a professional course with IBM and they provided us with the data to load.

Please help me! :)

LUW DB2 JDBC Connection Issue on Open JDK 11

I am not able to connect LUW DB2 database using JDBC Type4 driver on Open Zulu JDK 11.

I have tried all available latest DB2 JDBC Drivers (Type 4) but same issue with all drivers on JDK 11. Things are working fine on JDK 8.

Getting below error

Exception in thread "main" java.lang.NumberFormatException: For input string: "B" at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) at java.base/java.lang.Integer.parseInt(Integer.java:652) at java.base/java.lang.Integer.parseInt(Integer.java:770) at com.ibm.db2.jcc.c.xc.(xc.java:64) at com.ibm.db2.jcc.b.f.(f.java:44) at com.ibm.db2.jcc.b.b.cb(b.java:1945) at com.ibm.db2.jcc.c.p.a(p.java:3284) at com.ibm.db2.jcc.b.b.a(b.java:625) at com.ibm.db2.jcc.b.b.(b.java:334) at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:165) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228) at com.aonhewitt.Test.main(Test.java:14)

Test Java Code

Class.forName("com.ibm.db2.jcc.DB2Driver");Connection conn = DriverManager.getConnection("jdbc:db2://hostname:port/database","userid","password");

I would appreciate if anyone can help me here to connect to a DB2-LUW database from JDK 11

JDBC Kafka connector for DB2, TIME field with New York timezone fails with times above 19:00:00

Our team is using the confluent DB2 connector. The source points to an on-prem system using New York (Eastern Time). There is a GAME_TIME field of type TIME in DB2. Whenever this TIME > '19:00:00', our defined source fails with

connect | 2020-10-20T13:56:16.868034900Z org.apache.kafka.connect.errors.ConnectException: Tolerance exceeded in error handlerconnect | 2020-10-20T13:56:16.868068100Z at org.apache.kafka.connect.runtime.errors.RetryWithToleranceOperator.execAndHandleError(RetryWithToleranceOperator.java:178)connect | 2020-10-20T13:56:16.868091300Z at org.apache.kafka.connect.runtime.errors.RetryWithToleranceOperator.execute(RetryWithToleranceOperator.java:104)connect | 2020-10-20T13:56:16.868190500Z at org.apache.kafka.connect.runtime.WorkerSourceTask.convertTransformedRecord(WorkerSourceTask.java:295)connect | 2020-10-20T13:56:16.868213800Z at org.apache.kafka.connect.runtime.WorkerSourceTask.sendRecords(WorkerSourceTask.java:321)connect | 2020-10-20T13:56:16.868237000Z at org.apache.kafka.connect.runtime.WorkerSourceTask.execute(WorkerSourceTask.java:245)connect | 2020-10-20T13:56:16.868259700Z at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:184)connect | 2020-10-20T13:56:16.868282900Z at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:234)connect | 2020-10-20T13:56:16.868326800Z at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)connect | 2020-10-20T13:56:16.868498300Z at java.util.concurrent.FutureTask.run(FutureTask.java:266)connect | 2020-10-20T13:56:16.868524300Z at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)connect | 2020-10-20T13:56:16.868546700Z at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)connect | 2020-10-20T13:56:16.868570300Z at java.lang.Thread.run(Thread.java:748)connect | 2020-10-20T13:56:16.868592900Z Caused by: org.apache.kafka.connect.errors.DataException: Kafka Connect Time type should not have any date fields set to non-zero values.connect | 2020-10-20T13:56:16.868616000Z at org.apache.kafka.connect.data.Time.fromLogical(Time.java:64)connect | 2020-10-20T13:56:16.868639300Z at org.apache.kafka.connect.json.JsonConverter$15.toJson(JsonConverter.java:248)connect | 2020-10-20T13:56:16.868682800Z at org.apache.kafka.connect.json.JsonConverter.convertToJson(JsonConverter.java:606)connect | 2020-10-20T13:56:16.868706200Z at org.apache.kafka.connect.json.JsonConverter.convertToJson(JsonConverter.java:693)connect | 2020-10-20T13:56:16.868729200Z at org.apache.kafka.connect.json.JsonConverter.convertToJsonWithEnvelope(JsonConverter.java:581)connect | 2020-10-20T13:56:16.868752200Z at org.apache.kafka.connect.json.JsonConverter.fromConnectData(JsonConverter.java:335)connect | 2020-10-20T13:56:16.868775300Z at org.apache.kafka.connect.storage.Converter.fromConnectData(Converter.java:63)connect | 2020-10-20T13:56:16.868823000Z at org.apache.kafka.connect.runtime.WorkerSourceTask.lambda$convertTransformedRecord$2(WorkerSourceTask.java:295)connect | 2020-10-20T13:56:16.868846300Z at org.apache.kafka.connect.runtime.errors.RetryWithToleranceOperator.execAndRetry(RetryWithToleranceOperator.java:128)connect | 2020-10-20T13:56:16.868868300Z at org.apache.kafka.connect.runtime.errors.RetryWithToleranceOperator.execAndHandleError(RetryWithToleranceOperator.java:162)connect | 2020-10-20T13:56:16.868891500Z ... 11 moreconn

The following source connector definition works fine:

{ "name": "test_before_1900", "config": { "_comment": "before 1900", "connector.class": "io.confluent.connect.jdbc.JdbcSourceConnector", "key.converter": "org.apache.kafka.connect.storage.StringConverter", "value.converter": "org.apache.kafka.connect.json.JsonConverter", "connection.url": "jdbc:db2://$dbHost:50000/$dbName", "connection.user": "$dBUser", "connection.password": "$dbPassword", "query": "SELECT * FROM (SELECT GAME_ID, GAME_TIME, UPDATED_TS FROM GAME WHERE GAME_TIME < '19:00:00')", "mode": "timestamp+incrementing", "timestamp.column.name": "UPDATED_TS", "timestamp.delay.interval.ms": "3000", "incrementing.column.name": "GAME_ID", "validate.non.null": "false", "topic.prefix": "db2-before-", "db.timezone": "America/New_York" }}

...while this one fails with the above error.

{ "name": "test_after_1900", "config": { "_comment": "after 1900", "connector.class": "io.confluent.connect.jdbc.JdbcSourceConnector", "key.converter": "org.apache.kafka.connect.storage.StringConverter", "value.converter": "org.apache.kafka.connect.json.JsonConverter", "connection.url": "jdbc:db2://$dbHost:50000/$dbName", "connection.user": "$dBUser", "connection.password": "$dbPassword", "query": "SELECT * FROM (SELECT GAME_ID, GAME_TIME, UPDATED_TS FROM GAME WHERE GAME_TIME >= '19:00:00')", "mode": "timestamp+incrementing", "timestamp.column.name": "UPDATED_TS", "timestamp.delay.interval.ms": "3000", "incrementing.column.name": "GAME_ID", "validate.non.null": "false", "topic.prefix": "db2-after-", "db.timezone": "America/New_York" }}

I understand why, from the source code in kafka, it fails. It's this line in Kafka.

 if (unixMillis < 0 || unixMillis > MILLIS_PER_DAY) { throw new DataException("Kafka Connect Time type should not have any date fields set to non-zero values."); }

My question is, can I work around this without changing kafka itself or horribly mangling my data out of Eastern Time?

Assigning variable to session nodejs

I have created an authentication system using db2 for database, and nodejs for backend code. Yes, I know I could have used passport, but I wanted to try to build this auth system without it. I have everything working, and the way I am making sure users are logged into the system before it redirects to the desired page is checking if a global variable value is = true, and if it is, render the page. The problem is, the value of the variable is not unique to every session. I'm wondering if the best way to go about this is somehow setting the variable unique to every session, or maybe using db2 somehow. Thanks in advance for the help :)

Can't connect to DB2 with DataGrip with securityMechanism=13

I'm trying to make a connection with the DB2 database with DataGrip. For the connection securityMechanism=13 is needed.

The connection failed with the following message:

[-4223] [jcc][1071][10615][3.68.61] Caughtjava.security.InvalidAlgorithmParameterException while initializingEncryptionManager. See attached Throwable for details.ERRORCODE=-4223, SQLSTATE=null DH key size must be multiple of 64, andcan only range from 512 to 8192 (inclusive). The specific key size 256is not supported.

We used the following string:

 jdbc:db2://:/:securityMechanism=13;

When we disable the security measures we can connect to the database, but as soon as the ENCRYPTED_USER_PASSWORD_AND_DATA_SECURITY is enabled, we can't setup a connection.

We also use DataStudio and with this application it is possible to create a jdbc connection to the DB2 database. We prefer to use DataGrip because we need to maintain different types of databases which is not possible in DataStudio.

Different NuGet package based on operating system

I have a test project in dotnet 2.1 that needs to work across multiple platforms (specifically, windows and linux-based systems) as well as access DB2 databases.

IBM provides separate NuGet packages for different operating systems:

  1. IBM.Data.DB2.Core
  2. IBM.Data.DB2.Core-lnx
  3. IBM.Data.DB2.Core-osx

How can I specify in my .csproj file that I want to use different packages based on the operating system?

Passing in the RuntimeIdentifier (dotnet publish ... -r linux-x64) is possible, but I am unsure how to leverage that information inside the csproj. I am also not opposed to using the Choose/When construct, but don't know how to infer what system is trying to build the project.

Inserting Char value to database column having DBCLOB 2g in DB2

Logic 1:

Dcl-s WkoutJSON Char(16000000); 

This wkoutJSON field contains built JSON value

Then I am trying to insert to Database

Insert into TBLPOST(TBLPAYLD) Values( Method 1 // cast(:wkoutJSON as dbclob(2g) ccsid 1200) Method 2 // DBCLOB(:wkoutJSON, 1073741823, CODEUNITS32));

I have tried in two ways. both are not working getting wkoutJSON is not declared or not usable

Logic 2:

Dcl-s WkoutJSON Char(16000000); Dcl-s DbClobVar SQLTYPE(DBCLOB:8386550); 

This wkoutJSON field contains built JSON value

DbClobVar_Data = %Trim(%Graph(wkoutJSON));DbClobvar_Len = %Len(DbClobVar_Data); 

Then I am trying to insert to Database

Insert into TBLPOST(TBLPAYLD) Values(:DbClobvar);

Not working getting wkoutJSON is not declared or not usable

Kindly help me to insert the char values to dbclob.

Iterate through FOR CURSOR in Python

I just started with coding in Python and I have one problem. I am using ibm_dbi driver.

Here is my sql example:

 FOR v AS cur CURSOR FOR SELECT S.NAME FROM SYSIBM.SYSTABLES AS S JOIN (SELECT DISTINCT TBNAME FROM SYSIBM.SYSCOLUMNS WHERE CREATOR = 'SOME_SCHEMA_DB') AS C.TBNAME = S.NAME WHERE S.CREATOR = 'SOME_SCHEMA' AND S.TYPE = 'T' DO SET TABLE = SCHEMA1 ||'.'||v.Name ... SELECT 1 INTO TEMP_TABLE FROM SYSIBM.SYSTABLES WHERE TYPE='T' AND CREATOR = SCHEMA1 AND NAME = v.NAME END FOR;

So far i have in Python:

#make a connection to dbconn = ibm_db_dbi.connect("DATABASE=%s;HOSTNAME=%s,....)#define a cursorcur = conn.cursor()sql="""SELECT S.NAME FROM SYSIBM.SYSTABLES AS S JOIN (SELECT DISTINCT TBNAME FROM SYSIBM.SYSCOLUMNS WHERE CREATOR = 'SOME_SCHEMA_DB') AS C.TBNAME = S.NAME WHERE S.CREATOR = 'SOME_SCHEMA' AND S.TYPE = 'T'"""resultSet = cur.execute(sql)

I don't know how to iterate through result from query and set values from the cursor. How to set value for this piece of code

SET TABLE = SCHEMA1 ||'.'||v.Name ...SELECT 1 INTO TEMP_TABLE FROM SYSIBM.SYSTABLES WHERE TYPE='T' AND CREATOR = SCHEMA1 AND NAME = v.NAME
DAL01013: Cannot connect to server

I'm trying to connect to DB2 db but getting the error:

DAL01013. Cannot create connection to server 'Unknown server' due to the following: [jcc][t4][2034][11148][3.69.24] Execution failed due to a distribution protocol error that caused deallocation of the conversation.A DRDA Data Stream Syntax Error was detected. Reason: 0x3. ERRORCODE=-4499, SQLSTATE=58009

Assume it's something with my system. I cannot connect neither via DataStudio nor via QMF. Other users from other system can. I use same jdbc drivers. My system is Windows 7.Is there anything else I need to adjust on my system?

DB2 external java jar file stored procedure error

I have been trying to call a java jar file from DB2 stored procedure as external jar.Below is my external jar file code.

package connection; public class Connect { static Connection con = null; static Statement stmt = null; public static void main(String[] args) throws SQLException { javastp("v1"); } public static void javastp( String name) throws SQLException{ try { con = DriverManager.getConnection( "jdbc:default:connection" ); stmt = con.createStatement(); stmt.executeUpdate("INSERT INTO SCHEMA_NAME.TEST(NAME, FLAG) VALUES ('"+name+"',true) "); }catch (Exception e) { // ... }finally { //Close open resources if (stmt != null) stmt.close(); if (con != null) con.close(); } } }

i install it using below command in db2 server

db2 call sqlj.install_jar('file:E:/jarpath../jarname.jar','jarname',0); 

Below is the stored procedure for DB2 external jar.

CREATE OR REPLACE PROCEDURE SCHEMA_NAME.PROC6() LANGUAGE java PARAMETER STYLE java FENCED EXTERNAL NAME 'jarname:connection.Connect.main'

The stored procedure executes successfully without errors.

But when i try to call the stored procedure using below,

CALL SCHEMA_NAME.PROC6() 

i get the below error

SQL Error [38503]: A stored procedure process has been terminated abnormally. Routine name: "SCHEMA_NAME.PROC6". Specific name: "SQL201016144125554".. SQLCODE=-1131, SQLSTATE=38503, DRIVER=4.8.86

The code works fine when executed independently and the records are being inserted into the table, but i cant seem to figure out why the stored procedure gives me error when i try to call it using the db2 stored procedure.

Can somebody here please help me out as am stuck on this since a very long time and i have no background of DB2.

the db2level command gives the below output:

DB21085I This instance or install (instance name, where applicable: "DB2")uses "64" bits and DB2 code release "SQL10058" with level identifier"0609010E".Informational tokens are "DB2 v10.5.800.381", "s160901", "IP24000", and FixPack "8".Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".

Linux Suse db2 version 11.1.2Database server = DB2/LINUXX8664 11.1.2