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 external stored procedure fails with CPF9810 when called from client

In a green screen session, caling a program MYLIB/TESTPRG works when my library list is set to QGPL, QTEMP, VENDRLIB1, VENDRLIB2, VENDRLIB3. I can execute call MYLIB/TESTPRG on a green screen command line.

I want to be able to run this command from my Windows client. I created an external stored procedure MYLIB/TESTPROC with external name MYLIB/TESTPRG as I have seen in various articles. My original question stated that I could execute this procedure successfully in STRSQL in a green screen session with my library list as above, but that is false. It does not work. It simply says 'Trigger program or external routine detected an error.' Sorry for the wrong information.

When MYLIB/TESTPROC is called from the client (CALL MYLIB/TESTPROC), it fails with CPF9810 (Library &1 not found). I connected to the database via i Navigator -> Run SQL Scripts. In Connection -> JDBC Settings I had Default SQL schema = 'Use library list of server job' and set Schema list=QGPL,QTEMP,VENDRLIB1,VENDRLIB2,VENDRLIB3. I then executed CALL MYLIB/TESTPROC and got the message as stated above.

What works is when I run the program, i.e. CALL MYLIB/TESTPRG on a green screen command line.

TESTPRG is a C program that takes no arguments. The stored procedure was defined like this:

CREATE PROCEDURE MYLIB/TESTPROC LANGUAGE C SPECIFIC MYLIB/TESTPROC NOT DETERMINISTIC NO SQL CALLED ON NULL INPUT EXTERNAL NAME 'MYLIB/TESTPRG' PARAMETER STYLE GENERAL ;
Can I change the datatype and max length of a column in Table B that I'm creating with a CREATE TABLE AS SELECT FROM Table A?

I would like to have the converted date formats to be in the ResultB Table instead of the CHAR type dates and I was looking for a way to do this inside the create table state in 1 go.

I have successfully created a new Table ResultB with a subset of data from source Table SourceA.There is a column UPLSTD (CHAR type) that contains dates, in order to compare with the current date I converted the CHAR type dates with a to_date function, this also works.

CREATE TABLE ResultB AS (SELECT UPUPRF, UPTEXT, UPLSTD FROM SourceA WHERE to_date(SUBSTR(DIGITS(UPLSTD), 27, 6), 'RRMMDD') < CURRENT_DATE - 10 DAYS) WITH DATA

I want the output to be:

UPUPRF UPTEXT UPLSTD (DATE Format)------ -------- --------------------ABCD ABCDEFG 2019-08-21

But the actual is:

UPUPRF UPTEXT UPLSTD (CHAR Format)------ -------- --------------------ABCD ABCDEFG 190821
Converting milliseconds since midnight to readable timestamp

I have an application that logs the time an action occurred in milliseconds since midnight and stores it in a Db2 10.5 database table. I need to convert that figure to a human-readable timestamp output.

SELECT ACTION_NAME, ACTION_TIMEFROM ACT.ACTIONSALARM1 1818000ALARM2 76500000 

I want to be able to run a SELECT but have the value returned like

ALARM1 05:03ALARM2 21:15

Any guidance would be appreciated.

Connecting from Tomcat to AS400 : java.lang.UnsupportedOperationException: Not supported by BasicDataSource

I have already gone through most of the java.lang.UnsupportedOperationException: Not supported by BasicDataSource articles on Stack Overflow and other sites. Nothing worked for me.

I'm trying to migrate an application that is currently working in IBM Websphere Application Server to Tomcat.

This application is using a proprietary jar file which creates the DataSource object and calls the datasource.getConnection(). I used a decompiler to see the source code and found this:

enter image description here

On debugging, I found that, this.datasource is getting org.apache.tomcat.dbcp.dbcp2.BasicDataSource object assigned to it instead of the AS400JDBCDataSource object. I have added the jt400 jar file (which contains this class) in project lib as well as tomcat lib.

AS400JDBCDataSource class implements DataSource and has it's own implementation of getConnection which is what I need to be called, but that's not happening.

enter image description here

I'm getting this error while tomcat starts :

Root cause:java.lang.UnsupportedOperationException: Not supported by BasicDataSource at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:829)

How can I debug this issue?

Trying to run dynamic sql using a UDF in DB2

I am very new to DB2 even though have experience in Oracle. I am not able to resolve this issue.I have a requirement where I need to find missing child records in the parent table .The parent table , child table and the join_key are all passed as input parameter.

I have tried this in a procedure was able to achieve this, but the admin wants it in a function so that they can just use it in a select statment and get the result in a table format. Since the parent table , child table and the join_key are comming as input parement, I am not able to run them as dynamic sql.

create or replace function missing_child_rec(PARENT_TBL VARCHAR(255),JOIN_KEY VARCHAR(255),CHILD_TBL VARCHAR(255))RETURNS TABLE(Key VARCHAR(255))LANGUAGE SQLBEGINDECLARE V_SQL VARCHAR(500);DECLARE C_SQL CURSOR WITH RETURN FOR S_SQL;SET V_PARENT_TAB = PARENT_TBL;SET V_KEY = JOIN_KEY;SET V_CHILD_TAB = CHILD_TBL;SET V_SQL = 'SELECT DISTINCT '|| JOIN_KEY || ' FROM ' || V_CHILD_TAB || ' A WHERE NOT EXISTS (SELECT ' ||V_KEY || ' FROM ' || V_PARENT_TAB || ' B WHERE A.'||JOIN_KEY || '= B.'||JOIN_KEY ||' )' ;PREPARE S_SQL FROM V_SQL;OPEN C_SQL;CLOSE C_SQL;RETURNEND

When I try to compile it , it says prepare is invalid , I have tried even execute immediate but that also gave error.Can you please help me with how to use dynamic sql in UDF or an alternative logic for this problem

Group by category,store and attribute and assign sequence based on alphabetical order of attribute name

In a DB2 database I have 2 tables

 attribute --------- attribute_id name 1002 xyz 1012 abc 1023 lmn 1057 def 1076 qwe storecatattrrel --------------- store_id category_id attribute_id 100 10007 1012 100 10007 1057 100 10007 1023 101 10005 1002 101 10006 1002 101 10007 1057 101 10007 1002 101 10007 1023 101 10007 1076

I am trying to create an SQL query using which I want to assign sequence value to attributes in category-store-attribute relationship based on alphabetical order of attribute name like below.

 store_id category_id attribute_id sequence 100 10007 1012 1 100 10007 1057 2 100 10007 1023 3 101 10005 1002 1 101 10006 1002 1 101 10007 1057 1 101 10007 1002 4 101 10007 1023 2 101 10007 1076 3

I tried using ROW_NUMBER function but without any luck.

select b.store_id, b.category_id, b.attribute, a.name, ROW_NUMBER() OVER(partition by attribute_id) from storecatattrrel b inner join attribute a on a.attribute_id = b.attribute_id order by name asc;

I could not find a way to group the results based on store_id, category_id and attribute_id and then assign the sequence.

Any help would be appreciated!

java.lang.ClassNotFoundException: com.ibm.websphere.rsadapter.DB2AS400DataStoreHelper

I am trying to migrate an application from Websphere to Tomcat and facing an issue.

Class not found: com.ibm.websphere.rsadapter.DB2AS400DataStoreHelper

I have this jar com.ibm.ws.runtime-8.5.5.1 in the lib, but it doesn't have this particular DB2AS400DataStoreHelper class inside rsadapter package.

Can someone please help me with the jar or the version number which has DB2AS400DataStoreHelper class?

4:23:42 PM: Caused by: com.ibm.ws.rsadapter.exceptions.DataStoreAdapterException: DSRA8050W: Unable to find the DataStoreHelper class specified: com.ibm.websphere.rsadapter.DB2AS400DataStoreHelper at com.ibm.ws.rsadapter.AdapterUtil.createDataStoreAdapterException(AdapterUtil.java:441) at com.ibm.ws.rsadapter.DSConfigHelper.createDataStoreHelper(DSConfigHelper.java:797) at com.ibm.ws.rsadapter.spi.WSRdbDataSource.(WSRdbDataSource.java:1153) at com.ibm.ws.rsadapter.spi.WSManagedConnectionFactoryImpl.setDataSourceProperties(WSManagedConnectionFactoryImpl.java:2644) ... 72 moreCaused by: java.lang.ClassNotFoundException: com.ibm.websphere.rsadapter.DB2AS400DataStoreHelper at java.net.URLClassLoader.findClass(Unknown Source) at com.ibm.ws.bootstrap.ExtClassLoader.findClass(ExtClassLoader.java:204) at java.lang.ClassLoader.loadClass(Unknown Source) at com.ibm.ws.bootstrap.ExtClassLoader.loadClass(ExtClassLoader.java:119) at java.lang.ClassLoader.loadClass(Unknown Source)
Running db2relocatedb with ansible

I have this ansible playbook:

 --- - hosts: all gather_facts: False become: yes become_user: myins become_method: sudo tasks: - name: test someting command: "cd /db2/myins" - name: relocate it command: "db2relocatedb -f /db2/myins/relocate.cfg" ...

if I run the command by itself it works and if I use cat /db2/myins/relocate.cfg instead of db2relocatedb -f it works too.

If I run it like this I get:

 The full traceback is: WARNING: The below traceback may *not* be related to the actual failure. File "/tmp/ansible_command_payload_HGHkvR/ansible_command_payload.zip/ansible/module_utils/basic.py", line 2561, in run_commandcmd = subprocess.Popen(args, **kwargs)File "/usr/lib64/python2.7/subprocess.py", line 711, in __init__errread, errwrite)File "/usr/lib64/python2.7/subprocess.py", line 1327, in _execute_childraise child_exceptionfatal: [mounttest]: FAILED! => {"changed": false,"cmd": "db2relocatedb -f /db2/myins/relocate.cfg","invocation": { "module_args": { "_raw_params": "db2relocatedb -f /db2/myins/relocate.cfg", "_uses_shell": false, "argv": null, "chdir": null, "creates": null, "executable": null, "removes": null, "stdin": null, "stdin_add_newline": true, "strip_empty_ends": true, "warn": true }},"msg": "[Errno 2] No such file or directory","rc": 2 }

Why can I cat the file but not use it?

Thanks,Arengin

how to retrieve JSON data stored in CLOB Datatype in DB2?

I have JSON data stored in CLOB in DB2 database. I want to query this JSON data and retrieve some data .

I tried with JSON_VAL and get this error

DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=JSON_VAL.

can anyone please let me know if I can use JSON_VAL if not right one with an example helps.

Running DB2 client container as non root user

I am building a docker container with db2 client installed. Following this instruction I am able to build a docker image with db2 client installed. Here is the dockerfile:

## DB2 10.5 Client Dockerfile (Part 1)## Requires# - DB2 10.5 Client for 64bit Linux ibm_data_server_runtime_client_linuxx64_v10.5.tar.gz# - Response file for DB2 10.5 Client for 64bit Linux db2rtcl_nr.rsp ### Using Ubuntu 14.04 base image as the starting point.FROM ubuntu:14.04MAINTAINER David Carew # DB2 prereqs (also installing sharutils package as we use the utility uuencode to generate password - all others are required for the DB2 Client) RUN dpkg --add-architecture i386 && apt-get update && apt-get install -y sharutils binutils libstdc++6:i386 libpam0g:i386 && ln -s /lib/i386-linux-gnu/libpam.so.0 /lib/libpam.so.0# Create user db2clnt# Generate strong random password and allow sudo to root w/o password#RUN \ adduser --quiet --disabled-password -shell /bin/bash -home /home/db2clnt --gecos "DB2 Client" db2clnt && \ echo db2clnt:`dd if=/dev/urandom bs=16 count=1 2>/dev/null | uuencode -| head -n 2 | grep -v begin | cut -b 2-10` | chgpasswd# Install DB2RUN mkdir /install# Copy DB2 tarball - ADD command will expand it automaticallyADD ibm_data_server_runtime_client_linuxx64_v10.5.tar.gz /install/# Copy response fileCOPY db2rtcl_nr.rsp /install/# Run DB2 silent installerRUN /install/rtcl/db2setup -u /install/db2rtcl_nr.rsp # Clean up unwanted filesRUN rm -fr /install/rtcl# Login as db2clnt userCMD su - db2clnt

But I face a problem that when I run it in the kubernetes environment, it can not be started because the container there can be run only as non-root user. Following log is displayed:

su: must be run from a terminal

I can not change the config in the kubernetes environment therefore I have to change my dockerfile to start the container as non-root user. I tried to change in the dockerfile the CMD su - db2clnt to USER db2clnt but without success. Can anyone give me some ideas here how to run it as non-root user? Thank you very much!