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.

 

 
ROW_NUMBER() in DB2

How to use ROW_NUMBER() in where clause in DB2 database. I have tried below but it did not work:

SELECT * FROM CSPAPP.LOCATIONSWHERE (ROW_NUMBER() OVER(ORDER BY LOCATION)) BETWEEN 100 AND 200

It gave error : Invalid use of aggregate function or OLAP function.

I also tried with followiong ways :

SELECT (ROW_NUMBER() OVER(ORDER BY LOCATION)) AS RN ,* FROM CSPAPP.LOCATIONSWHERE RN < 200SELECT (ROW_NUMBER() OVER(ORDER BY LOCATION)) AS RN ,LOCATION FROM CSPAPP.LOCATIONSWHERE RN < 200
Python DB2 - Symbol not found

After updating to Mac Catalina (version 10.15.6), I had to re-install Python and all dependent modules.My problem now is that when adding the module ibm_db, I get the following error message when trying to run my program:

ImportError: dlopen(/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/ibm_db.cpython-37m-darwin.so, 2): Symbol not found: ___cxa_throw_bad_array_new_length Referenced from: /Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/clidriver/lib/libdb2.dylib Expected in: /usr/lib/libstdc++.6.dylib in /Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/clidriver/lib/libdb2.dylib

I am using PyCharm 2018.3.7 (CE).

Anyone that has seen this, and have a fix for this?

DB2 interpret poorly formatted date

I have a tool which is populating a merge field I am using as part of generated SQL. The format is 113 (04 Aug 2020) and I cannot change it. What is the best way to make DB2 interpret this as a date?

The file system is full.. SQLCODE=-968, SQLSTATE=57011, DRIVER=4.18.60

When I try to run a long query in DB2, I am getting the below error:

"The file system is full SQLCODE=-968, SQLSTATE=57011, DRIVER=4.18.60"

There is lots of memory available in C drive. So memory is not an issue.

There are 65 table spaces available in the database.

It works fine for other smaller queries but for one long query, I am getting this message repeatedly in DB2.

I have tried multiple solutions but with no luck.

Please help to fix this issue.

Skip creating JSON_OBJECT if there is no data

I am working on creating a single JSON_OBJECT which has multiple layers that should not create a JSON_OBJECT if there is no matching data. In the example, I am showing how I am creating it with skipping a level if there is no data, but I am hoping there is a better and simpler way to do this as I cannot get this way to work with the current issue. The ending place for this JSON can not handle null JSON_OBJECTS, so they would like them excluded from the file.

This is some sample data:Condition:Condition

Action:Action

As you can see there is no matching action for one of the conditions.This is how I am currently creating the JSON, which does not remove the extra JSON_OBJECT as needed, but does show how I am creating one level if needed for a foreign language.

 With -- Create All of the (actions) fAction1 as ( Select CONCCD, CONDID, Case when PADSecLangCode = '' then json_object( 'actionID' : trim(ACTID), 'actionDescription' : trim(DESC), 'active' : ACTIVE) Else json_object( 'actionID' : trim(ACTID), 'actionDescription' : trim(DESC), 'active' : ACTIVE, 'recordTexts' : json_object( 'recordText' : json_array( json_object( 'languageID' : trim(PADSecLangCode), 'actionDescription' : trim(DESCF), 'active' : ACTIVE ))) ) End as sAction1 From PADWCA) -- Create All of the Conditions (conditions/actions) , fCondition1 as ( Select CONCCD, Case when PADSecLangCode = '' then json_object( 'conditionID' : trim(CONDID), 'conditionDescription' : trim(DESC), 'active' : ACTIVE, 'actions' : json_object( 'action' : json_array( (Select sAction1 From fAction1 a Where a.CONCCD = c.CONCCD and a.CONDID = c.CONDID ) format json ) format json ) format json ) Else json_object( 'conditionID' : trim(CONDID), 'conditionDescription' : trim(DESC), 'active' : ACTIVE, 'recordTexts' : json_object( 'recordText' : json_array( json_object( 'languageID' : trim(PADSecLangCode), 'conditionDescription' : trim(DESCF), 'active' : ACTIVE ))), 'actions' : json_object( 'action' : json_array( (Select sAction1 From fAction1 a Where a.CONCCD = c.CONCCD and a.CONDID = c.CONDID ) format json ) format json ) format json )End as sCondition1 From PADWCC c) 

-- Create a wrapper around Condion/action, fCondition2 as (select json_object('condition': json_arrayagg(sCondition1 format json)) as sCondition2from fCondition1)

-- Create the final outcome
Select json_object('conditions' : sCondition2 format json)From fCondition2;

This is the results that I currently get:

{"conditions": { "condition": [ { "conditionID": "038-00068-C32", "conditionDescription": "Exclusion service repairs were made to the following areas:", "active": "false", "recordTexts": { "recordText": [ { "languageID": "FR", "conditionDescription": "Service de réparations d'exclusion ont été faites aux domaines suivants:", "active": "false" } ] }, "actions": { "action": [] } }, { "conditionID": "020-00050-C26", "conditionDescription": "The area was very clean and in excellent condition!", "active": "false", "recordTexts": { "recordText": [ { "languageID": "FR", "conditionDescription": "La zone était très propre et en excellent état!", "active": "false" } ] }, "actions": { "action": [ { "actionID": "001-051-C26", "actionDescription": "Please thank everyone who cleaned the area!", "active": "false", "recordTexts": { "recordText": [ { "languageID": "FR", "actionDescription": "'il vous plaît remercier tous ceux qui nettoyé la zone!", "active": "false" } ] } } ] } } ]}

}

This is the results that I am trying to get:

{"conditions": { "condition": [ { "conditionID": "038-00068-C32", "conditionDescription": "Exclusion service repairs were made to the following areas:", "active": "false", "recordTexts": { "recordText": [ { "languageID": "FR", "conditionDescription": "Service de réparations d'exclusion ont été faites aux domaines suivants:", "active": "false" } ] }, }, { "conditionID": "020-00050-C26", "conditionDescription": "The area was very clean and in excellent condition!", "active": "false", "recordTexts": { "recordText": [ { "languageID": "FR", "conditionDescription": "La zone était très propre et en excellent état!", "active": "false" } ] }, "actions": { "action": [ { "actionID": "001-051-C26", "actionDescription": "Please thank everyone who cleaned the area!", "active": "false", "recordTexts": { "recordText": [ { "languageID": "FR", "actionDescription": "'il vous plaît remercier tous ceux qui nettoyé la zone!", "active": "false" } ] } } ] } } ]}

}

Get a rows based on the Max value in one of the columns DB2 sql

Need your expertise with below scenario, any helps greatly appreciated.

Get a rows based on the Max value in one of the columns in DB2 sql

TABLE_1ORG DEST AccountNumber Amount Status -------------------------------------------------1224 6778 32345678 458.00 Accepted 1225 6779 12345678 958.00 Rejected 1226 6780 22345678 478.00 Rejected 1227 6781 21345678 408.00 Accepted TABLE_2ID NAME VERSION--------------------------1224 BankA 11224 BankA1 26778 TestBankA 16778 TestBankA1 26778 TestBankA1 31225 BankB 16779 TestBankB 16779 TestBankB1 26779 TestBankB2 36779 TestBankB3 41226 BankC 16780 TestBankC 11227 BankD 11227 BankD1 26781 TestBankD 1Expected OutputID AccountNumber Amount Status Origin Destination----------------------------------------------------------11 32345678 458.00 Accepted BankA1 TestBankA112 12345678 958.00 Rejected BankB TestBankB34 22345678 478.00 Rejected BankC TestBankC6 21345678 408.00 Accepted BankD1 TestBankD

With below query I am not seeing latest version bank name.

SELECT * FROM TABLE_1 AS T1INNER JOIN (SELECT ID, MAX(VERSION) FROM TABLE GROUP BY ID) AS T2ON T2.ID = T1.ORGINNER JOIN (SELECT ID, MAX(VERSION) FROM TABLE GROUP BY ID) AS T3ON T3.ID = T1.DESTWHERE Status <> 'Failed'
Fetching data from DB2 Views

Is fetching data from a DB2 view and DB2 table both are different or same please explain?What are the things will change in connection string?

DB2Driver.changeDB2Password returns invalid URL when password is 15 characters

I created a java tool using DB2Driver.changeDB2Password and it works successfully when the zOS password is 8 characters long. However, our zOS system implemented a minimum 15 character passphrase that users are switching to and when we attempt to use the tool to change a 15 character password, we get the following error:

Invalid database URL syntax: jdbc:db2://meuba.vipa.uk.ibm.com:446/EUBADB2A:retrieveMessagesFromServerOnGetMessage=true. ERRORCODE=-4461, SQLSTATE=42815

Code:

DB2Driver.changeDB2Password(url, user, new String(pwd), new String(newPwd));

URL: jdbc:db2://iccmvs2.pok.ibm.com:6009/DSN:retrieveMessagesFromServerOnGetMessage=true

Change the URL to 'jdbc:db2://iccmvs2.pok.ibm.com:6009' and it's successful. However, our new password phrase length is min 15 .. max 100 characters.

I can't find any documentation on the URL length. What's the URL length? or did I find a bug?

db2jcc4.jar : v11.1 : IBM Data Server Driver for JDBC and SQLJ 4.24.92

How to return Cursor as OUT parameter in DB2 for z/OS

I'm using DB2 for z/OS as my database. I have written one stored procedure in DB2 where it will return some result set. Currently I have declared one cursor and calling OPEN Cur at the end of the stored procedure. I,m calling my procedure from Java and I'm getting the result set using ResultSet resultSet = callableStatement.getResultSet();My SP is working for few hundred records. But getting failed when table contains millions of data:

Caused by: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error:SQLCODE=-904, SQLSTATE=57011, SQLERRMC=00C90084;00000100;DB2-MANAGEDSPACE WITHOUT SECONDARY ALLOCATION OR US, DRIVER=4.24.92

I want to know

  1. Is it possible to return Cursor as OUT parameter in my SP ?
  2. What is the difference between taking data using OPEN curs way and CURSOR as OUT parameter ?
  3. How to solve issue when data is huge ?
  4. Will CURSOR as OUT parameter solve the issue ?
How to connect internal private DB2 to Cognos Dynamic Dashboard Embedded on IBM Cloud

Im working on cognos dashboard embedded using the reference from -Cognos Dashboard embedded.

but instead of csv i'm working on JDBC data sources.i'm trying to connect to JDBC data source as -

"module": { "xsd": "https://ibm.com/daas/module/1.0/module.xsd", "source": { "id": "StringID", "jdbc": { "jdbcUrl": "jdbcUrl: `jdbc:db2://DATABASE-HOST:50000/YOURDB`", "driverClassName": "com.ibm.db2.jcc.DB2Driver", "schema": "DEFAULTSCHEMA" }, "user": "user_name", "password": "password" }, "table": { "name": "ROLE", "description": "description of the table for visual hints ", "column": [ { "name": "ID", "description": "String", "datatype": "BIGINT", "nullable": false, "label": "ID", "usage": "identifier", "regularAggregate": "countDistinct", }, { "name": "NAME", "description": "String", "datatype": "VARCHAR(100)", "nullable": true, "label": "Name", "usage": "identifier", "regularAggregate": "countDistinct" } ] }, "label": "Module Name", "identifier": "moduleId"}

Note - here my database is hosted on private network on not hosted on public IP address.

So when i add the above code to add datasources, then the data is not loading from my DB,even though i mentioned correct user and password for jdbc connection in above code then also when i drag and drop any field from data sources then it opens a pop up and which asks me for userID and Password.and even after i filled userID and Password details again in popup i'm still unable to load the data.

Errors -

1 . when any module try to fetch data then calls API -'https://dde-us-south.analytics.ibm.com/daas/v1/data?moduleUrl=%2Fda......'but in my case this API is failing and giving the error - Status Code: 403 Forbidden

  1. In SignOnDialog.jsAt line - 98 call for saveDataSourceCredential method fails and it says saveDataSourceCredential is not a function.

Expectation -It should not open a pop to asks for userID and password. and data will load directly just as it happens for database hosted on public IP domains.