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.

 

 
Facing issue in DB2 Sql error with SQLCODE=-181, SQLSTATE=22007, SQLERRMC=0;*N, DRIVER=3.61.75

I am facing an issue while using date formatting in where clause, while the same formatting works fine for another select query.

Working query using following condition in where clause:

select t1.x,t1.y,t2.z from t1 inner join t2 where TIMESTAMP(SUBSTR(20||t1.TRANSACTION_DATE,1,4)||'-'||SUBSTR(t1.TRANSACTION_DATE,3,2)||'-'||SUBSTR(t1.TRANSACTION_DATE,5,2)||' '||SUBSTR(t1.TRANSACTION_TIME,1,2)||':'||SUBSTR(t1.TRANSACTION_TIME,3,2)||':'||SUBSTR(t1.TRANSACTION_TIME,5,2)) BETWEEN '2018-06-01 00:00:00' AND '2018-06-18 12:01:00';

When the same query is used for t1 table and t3 table like:

select t1.x,t1.y,t3.z from t1 inner join t3 where TIMESTAMP(SUBSTR(20||t1.TRANSACTION_DATE,1,4)||'-'||SUBSTR(t1.TRANSACTION_DATE,3,2)||'-'||SUBSTR(t1.TRANSACTION_DATE,5,2)||' '||SUBSTR(t1.TRANSACTION_TIME,1,2)||':'||SUBSTR(t1.TRANSACTION_TIME,3,2)||':'||SUBSTR(t1.TRANSACTION_TIME,5,2)) BETWEEN '2018-06-01 00:00:00' AND '2018-06-18 12:01:00';

It does not work for the timestamp part.

Note: Transaction_date value is in '180618' format(yymmdd) in the table t1. Also the transaction_time is in 123030(hhmmss) format

Cursor select on 2nd Primary key on db2 sql field for embedded positional value - Unable to determine most efficient for longterm design

I have a SQL DB2 table where the first two fields are the primary keys (not including the third field which is date/time stamp). The table was designed by another team with the intent to make it generic. I was brought into the project after the key value for the second field was coded for when it was inserted onto the table. This leads me to this: We now have to do a cursor select with a WHERE clause that includes the first primary key -- and then for the second primary key it must be for only when it is a specific value in position 21 for 8 bytes. (And we will always know what that value will be for the second field.) The second field is a generic 70 byte field (alphanumeric). My question is should we use a LIKE wildcard for the WHERE clause statement for the second primary field condition or instead a SUBSTR since we know the position of the value? I ask because I have done an EXPLAIN yet I do not see a difference between the two (neither does my database analyst). And this is for a few million records for a 1300 byte long table. However, my concern is volume of the data on the table will grow on various systems. Therefore performance may become an issue. Just right now it is hard to measure the difference between LIKE and SUBSTR. But I would like to do my due diligence and code this for long term performance. And if there is a third option, please let me know.

Invalid argument for function integer IBM DB2

I need to filter out rows in table where numer_lini column has number in it and it is between 100 and 999, below code works just fine when i comment out line where i cast marsnr to integer. However when i try to use it i get error: Invalid character found in a character string argument of the function "INTEGER". when looking at the list seems like replace and translate filters only numbers just fine and select only contains legit numbers (list of unique values is not long so its easy to scan by eye). So why does it fail to cast something? I also tried using integer(marsnr), but it produces the same error. I need casting because i need numeric range, otherwise i get results like 7,80 and so on. As I mentioned Im using IBM DB2 database.

select numer_lini, war_trasy, id_prz1, id_prz2from alaska.trasawhere numer_lini in ( select marsnr from ( select distinct numer_lini marsnr from alaska.trasa where case when replace(translate(numer_lini, '0','123456789','0'),'0','') = '' then numer_lini else 'no' end <> 'no' ) where cast(marsnr as integer) between 100 and 999)fetch first 300 rows only 
global temporary table in db2 stored procedure

Just getting into DB2, and have decided to use global temporary table in my stored proc for my task.

The task would be the next: just populate some data for each day (during for example 5 days), selecting random rows from the other table I'm getting my date like:

 select id from ( select id, rand() rnd from source_table) where rnd>0 order by rnd fetch first 1000 rows only 

I wanted to store somewhere that list of int to reuse them. The idea was the next -

 create table test (id int, dt date); create or replace procedure proc1 () begin declare v_start date default '2018-05-25'; declare v_end date default '2018-05-30'; declare v_dml varchar(8000); /* this part so far doesn't work declare global temporary table session.temp_tab(id int) not logged on commit preserve; insert into session.temp_tab(id) select id from my_table;*/ while v_start <= v_end DO set v_dml = 'insert into test (id, dt) with t as ( select 1 id, '''||v_start||''' dt from sysibm.dual union select 2 id, '''||v_start||''' dt from sysibm.dual union select 3 id, '''||v_start||''' dt from sysibm.dual) select * from t where id in (1,3)'; /*instead of 1,3 I would like to have list of values in some temp table/array..., which I'll get from the other table and can just use duriing this proc I don't want to use sub select, because I'll get every time some random data. But also I need that list for filter in several insert/update statements*/ set v_start = v_start +1 day; execute immediate v_dml; commit; end while; end 

P.S. I use DB2 LUW v10.5.0.7

UPD_1:I would like to do DDL and DML operation in one loop. For example I want to add partititon and then insert the data to the same table. like this:

 create or replace procedure proc1 ( in in_rep int) begin declare v_dt date; declare v_end_dt date; declare v_add_part varchar(1024); declare v_id int; declare v_next_id int; select max(id), max(dt) into v_id, v_dt from f838.test; set v_end_dt = v_dt + in_rep day; while v_dt < v_end_dt DO set v_dt = v_dt +1 day; set v_next_id = v_id+1; set v_add_part = 'alter table TEST add PARTITION part_'||v_next_id||' starting from '||v_next_id||' ending at '||v_next_id; execute immediate v_add_part; insert into test (id, dt) select v_next_id, v_dt from sysibm.dual; end while; end

In this case I would get an error, SQLCODE=-327, SQLSTATE=22525THE ROW CANNOT BE INSERTED BECAUSE IT IS OUTSIDE THE BOUND OF THE PARTITION RANGE FOR THE LAST PARTITION.

Cause of I'm trying to alter table and insert in the same time, not step by step. But can't really get how to do it step by step excep of replace insert with dynamic sql, like:

 set v_add_part = 'alter table TEST add PARTITION part_'||v_next_id||' starting from '||v_next_id||' ending at '||v_next_id; set v_ins = 'insert into test (id, dt) select '||v_next_id||','''||v_dt||''' from sysibm.dual'; execute immediate v_add_part; execute immediate v_ins;
SQL Select DATES and assign accordingly

I'm using SQL Oracle;

I want to do the following operation, if the YEAR on the column is equal to my current year then I want to assign the value of 0 to a new column called ano, and if it's a year ahead than current year then I want it to be 1 and so on, if it's less than the current year then I want it to be -1 and so on.

The column name that contains the date is KALW_DATE

SELECT *, CASE WHEN YEAR(KALW_DATE) > YEAR(NOW()) THEN (YEAR(KALW_DATE) - YEAR(NOW())) WHEN YEAR(KALW_DATE) < YEAR(NOW()) THEN (YEAR(KALW_DATE) + YEAR(NOW()) ELSE 0 END as anoFROM tablename; 

but I get an error saying "keyword FROM not found where expected" also I'm connecting to DB2, if that changes anything.

php_ibm_db2.dll extension for PHP7.2 available for windows?

Has anybody managed to compile the php_ibm_db2.dll (ver 2.0.5) for Php7.2 on windows (x86 & x64)?

This Question is related to How do I compile ibm_db2 tar for PHP 7 on Windows? and php_ibm_db2.dll extension for PHP7 on Windows?

There is already a compiled php_ibm_db2.dll available for Php7.1, but not Php7.2 and I couldn't find any official or unofficial website/link/source

edit: thanks to @mao I managed to compile the dll and it seems to be working fine, I uploaded the php_ibm_db2-2.0.5-php7.2.6-ts-vc15-x86.zip here https://mega.nz/#!D8k2UCRB!indvDsREa5A6KfKkOugNHmBNyOFO5dZ5tQttxGdXh_M

Recurssive DB2 Sql query to be used in informatica Source Qualifier Transformation

i need to calculate metric values for 50 columns with same calculation pattern. i cannot use Union Query for all 50 columns as Source Qualifier in Informatica designer can hold upto 32767 characters.can anyone suggest ways TO achieve this ?

 SELECT djm.jobcode|| '_' || djm.job_region || '_' || md.srvy_country_cd || '_' || djm.match_year AS srvy_label, djm.match_year, 'NA' AS SRVY_PUBLISHER, 'Blend' AS cut, 'MI_UNIV_BLEND' AS srvy_name, md.srvy_country_cd, ( md.base_25 ) * ( 1 + djm.adjustment ) * ( djm.geo_diff ) AS adj_base_25, ( md.base_50 ) * ( 1 + djm.adjustment ) * ( djm.geo_diff ) adj_base_50, ( md.base_75 ) * ( 1 + djm.adjustment ) * ( djm.geo_diff ) AS adj_base_75, NULL AS adj_COMM_25, NULL AS adj_COMM_50, NULL AS adj_COMM_75, djm.weight, Sum(djm.weight) OVER (partition BY djm.jobcode,djm.job_region,djm.match_year) sum_weight, djm.weight*100 / Sum(djm.weight) OVER (partition BY djm.jobcode,djm.job_region,djm.match_year) adj_weight FROM md INNER JOIN djm ON djm.sk = md.sk AND djm.jobcode ='1234' AND djm.job_region ='USAB' AND djm.match_year ='2016' AND ( ( Nvl(md.base_25, 0) > 0 AND Nvl(md.base_50, 0) > 0 AND Nvl(md.base_75, 0) > 0 ) ) UNION SELECT djm.jobcode|| '_' || djm.job_region || '_' || md.srvy_country_cd || '_' || djm.match_year AS srvy_label, djm.match_year, 'NA' AS SRVY_PUBLISHER, 'Blend' AS cut, 'MI_UNIV_BLEND' AS srvy_name, md.srvy_country_cd, NULL AS adj_base_25, NULL AS adj_base_50, NULL AS adj_base_75, ( md.comm_25) * ( 1 + djm.adjustment ) * ( djm.geo_diff ) AS adj_COMM_25, ( md.comm_50) * ( 1 + djm.adjustment ) * ( djm.geo_diff ) AS adj_COMM_50, ( md.comm_75) * ( 1 + djm.adjustment ) * ( djm.geo_diff ) AS adj_COMM_75, djm.weight, Sum(djm.weight) OVER (partition BY djm.jobcode,djm.job_region,djm.match_year)sum_weight, djm.weight*100 / Sum(djm.weight) OVER (partition BY djm.jobcode,djm.job_region,djm.match_year) adj_weight FROM md INNER JOIN djm ON djm.sk = md.sk AND djm.jobcode ='12178' AND djm.job_region ='USAB' AND djm.match_year ='2016' AND ( ( Nvl(md.comm_25, 0) > 0 AND Nvl(md.comm_50, 0) > 0 AND Nvl(md.comm_75, 0) > 0 ) ) 
IBM DB2 Connection error with autoReconnect

I am using db2jcc4.jar version 4.19.26 and JDBC4 .

When I am adding autoReconnect=true in the connection URL ,

  jdbc:db2://host:port/db?autoReconnect=true com.ibm.db2.jcc.DB2Driver db2jcc4.jar .... 

I am getting the following error :

Caused by: javax.resource.ResourceException: Could not create connection at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:347) at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:354) at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:288) at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.createConnectionEventListener(SemaphoreArrayListManagedConnectionPool.java:1166) at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:446) at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getTransactionNewConnection(AbstractPool.java:541) at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:442) at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:379) ... 129 moreCaused by: com.ibm.db2.jcc.am.DisconnectNonTransientException: [jcc][t4][2034][11148][4.19.26] Execution failed due to a distribution protocol error that caused deallocation of the conversation.A DRDA Data Stream Syntax Error was detected. Reason: 0x2110. ERRORCODE=-4499, SQLSTATE=58009 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.t4.y.j(Unknown Source) at com.ibm.db2.jcc.t4.z.bb(Unknown Source) at com.ibm.db2.jcc.t4.z.y(Unknown Source) at com.ibm.db2.jcc.t4.z.T(Unknown Source) at com.ibm.db2.jcc.t4.z.i(Unknown Source) at com.ibm.db2.jcc.t4.z.a(Unknown Source) at com.ibm.db2.jcc.t4.b.c(Unknown Source) at com.ibm.db2.jcc.t4.b.b(Unknown Source) at com.ibm.db2.jcc.t4.b.b(Unknown Source) at com.ibm.db2.jcc.t4.b.a(Unknown Source) at com.ibm.db2.jcc.t4.b.a(Unknown Source) at com.ibm.db2.jcc.t4.b.a(Unknown Source) at com.ibm.db2.jcc.t4.b.(Unknown Source) at com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(Unknown Source) at com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(Unknown Source) at com.ibm.db2.jcc.DB2Driver.connect(Unknown Source) at com.ibm.db2.jcc.DB2Driver.connect(Unknown Source) at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:322) ... 136 more
IBM.Data.DB2.Core DataReader.GetSchemaTable() method throws NotSupportedException C# .NET standard

Code is in C# .NET standard

var connectionString = $"";var connection = new DB2Connection(connectionString);connection.Open();IDbCommand command = connection.CreateCommand();string sqlStatement = "";command.CommandText = sqlStatement;IDataReader dataReader = command.ExecuteReader();var data = new DataTable();data = dataReader.GetSchemaTable(); //This throws a NotSupportedException.//data.Load(dataReader); //This should work, but the above line is what throws the exception, which is what this method is calling internally.

A NotSupportedException is thrown on the last line that is not commented out, with the message 'Specified method is not supported.'

I have version 1.2.2.100 of IBM.Data.DB2.Core installed, and the version 11.1 license as well.If you need any more context let me know.

Thanks in advance.

How to exclude "?" for Null value from DB2 Select

I am extracting data from DB2 to Dataset via submitting the job(JCL) using DSNTIAUL. Some of column having null value but after extraction in dataset null value are placed like "?" For example: Table1:

the table structure

Select Query :

Select Column1,column2,Cloumn3from Table1;

Output Dataset :

AAAAA......................?.......BBBBBBCCCCC......DDDDDD.......................?

Could someone help to exclude "?" from the dataset. I tried with COALESCE but no luck. or should i need to write separate SORT step in JCL to remove "?".

And also is there any possible way extract data into CSV format