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 empty string equality comparison

I want to do equal comparison between empty strings('' = '')

Under query not work.I think ''='' does not work.

SELECT RESOURCE_ID FROM DEV_RESOURCEWHERE PHYSICAL_NAME = 'g' AND CASE WHEN '' = '' THEN '1' ELSE RESOURCE_DIV END = CASE WHEN '' = '' THEN '1' ELSE '' END -- AND DECODE('', '','1', RESOURCE_DIV) = DECODE('','','1','')WITH UR;

Like comment, changing query using decode function seems great but I don't want to.

DB2 jdbc connection error Connection refused. ERRORCODE=-4499, SQLSTATE=08001

I have a new DB2 server (v10.5.0.3), and I can connect to the database locally just fine.

When trying to connect from a remote server using JDBC I am getting the "Connection refused. ERRORCODE=-4499, SQLSTATE=08001" error. Based on information found here https://www-304.ibm.com/support/docview.wss?uid=swg21403644 I have confirmed that

[db2inst1@db2 ~]$ db2set -all[i] DB2COMM=TCPIP[i] DB2AUTOSTART=YES[g] DB2SYSTEM=db2.xxxx.com[g] DB2INSTDEF=db2inst1[g] DB2ADMINSERVER=xxxxxx

and

[db2inst1@db2 ~]$ db2 get database manager configuration | grep -i svce TCP/IP Service name (SVCENAME) = 50001 SSL service name (SSL_SVCENAME) = 

with these JDBC connection values

driver=com.ibm.db2.jcc.DB2Driverurl=jdbc:db2://db2.xxxxx.com:50001/TESTGENusername=XXXXXXXXpassword=XXXXXXX

I have verified that the firewall on the both servers have opened ports 50000 and 50001. I've run out of ideas, any help is greatly appreciated.

Select values from db2 table for null value and run even though it has value

I have a table student, in which marks can have null value, and it should update table if it has null values, otherwise it should skip the record

EXEC SQL select grade into math where marks is nullEND SQLif sql code = 000 exitelse error

for above code, I try running the file with values, then it is getting abended, can you help me with it?

IBM's DB2 Participation in Open-Source Software

I wanted to know if someone could provide me with some info on IBM's DB2 participation in Open-source software. I have to write a research paper on DB2 for a class of mine, however, I can't seem to find any info on DB2's participation in open source software. Please get back to me as soon as possible, thanks for your time.

Pipelines in BASH causing lost connections with db2
# it works finedb2 connect to  user  using while read alinedo tab=$(echo $aline | awk -F',' '{print $1}') col=$(echo $aline | awk -F',' '{print $2}') max_id=$(db2 -x "select count($col) from $tab") echo $tab $col $max_iddone < tab.ls# it doesn't work because of lost DB connectiondb2 connect to  user  using cat tab.lst | while read alinedo tab=$(echo $aline | awk -F',' '{print $1}') col=$(echo $aline | awk -F',' '{print $2}') max_id=$(db2 -x "select count($col) from $tab") echo $tab $col $max_iddone

I have two parts of codes, both have just little differences, the 1st part run as expected, but the 2nd did not.

It seems that pipelines caused losing DB connections.

Can anyone explain why.

Thanks in advance!

BTW: I tested in RH Linux 6.6, bash

Syntax error on DB2 XMLELEMENT

I get this error when trying out this command in the BIRT Classic Models sample database in Data Studio

select xmlelement(name "custno", customers.customernumber) from customers

Syntax error: Encountered "\"custno\"" at line 1, column 24.

I do not know how to correct it.

Thanks.

How to execute DB2 sql script that contains a CURSOR WITH RETURN FOR statement

Below is the sample PL/SQL block that i want to execute in a sql script.

BEGINDECLARE v_emp_nbr SMALLINT;BEGIN DECLARE RESULT_CURSOR CURSOR WITH RETURN FOR SELECT EMP_NAME, EMP_JOIN_DATE, EMP_DESIG FROM EMPLOYEE WITH UR FOR READ ONLY; OPEN RESULT_CURSOR ;END;END

I am receiving the below error when i try to execute through script.

42601(-109)[IBM][CLI Driver][DB2/AIX64] SQL0109N The "TO CALLER" clause is not allowed.

DB2: How do I display all dates in a range

I want to expand a range using DB2(on iSeries) query. For example, I have the following value in a table

2016-10-01 2016-10-03 600

I want the output as

2016-10-01 200 2016-10-02 2002016-10-03 200

I tried but I am not able to develop the query. It should be somewhere in similar lines as below.


Table (MYTABLE) has two columns. Below is snapshot

START_DT END_DT 2016-01-01 2016-01-03

On this query

with temp1 as ( SELECT start_dt, end_dt, start_dt as dt FROM mytable UNION SELECT start_dt, end_dt, dt + 1 day as dt FROM temp1 WHERE dt < end_dt ) SELECT dt FROM temp1 

I am getting the error "Column list not valid for table".

I tried this as well

with temp1 (start_dt, end_dt, dt) as ( SELECT start_dt, end_dt, start_dt as dt FROM mytable UNION SELECT start_dt, end_dt, dt + 1 day as dt FROM temp1 WHERE dt < end_dt ) SELECT dt FROM temp1 

This is throwing error "Keyword not allowed in recursive common table expression TEMP1."

DB2: How do I remove AUTO_INCREMENT?

How do I remove AUTO_INCREMENT of a column in DB2?

I tried

ALTER TABLE my_table ALTER my_id

but no luck. What is the correct SQL statement?

Getting Exception java.net.ConnectException while trying for DB2 SSL connection on RHEL

I am trying to establish a DB2 SSL connection on a Linux machine. I tried all the steps mentioned in the manual.

But when I am trying to connect to it through my client machine it is throwing an exception:

com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: [jcc][t4][2043][11550][4.21.29] Exception java.net.ConnectException: Error opening socket to server /10.66.61.29 on port 50,001 with message: Connection refused: connect. ERRORCODE=-4499, SQLSTATE=08001 at com.ibm.db2.jcc.am.kd.a(kd.java:338) at com.ibm.db2.jcc.am.kd.a(kd.java:435) at com.ibm.db2.jcc.t4.ac.a(ac.java:440) at com.ibm.db2.jcc.t4.ac.(ac.java:96) at com.ibm.db2.jcc.t4.a.b(a.java:366) at com.ibm.db2.jcc.t4.b.newAgent_(b.java:2076) at com.ibm.db2.jcc.am.Connection.initConnection(Connection.java:812) at com.ibm.db2.jcc.am.Connection.(Connection.java:754) at com.ibm.db2.jcc.t4.b.(b.java:339) at com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(DB2SimpleDataSource.java:233) at com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(DB2SimpleDataSource.java:199) at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:482) at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:116) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at com.psl.ssl.SSLTest1.main(SSLTest1.java:24) Caused by: java.net.ConnectException: Connection refused: connect at java.net.DualStackPlainSocketImpl.connect0(Native Method) at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source) at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source) at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source) at java.net.AbstractPlainSocketImpl.connect(Unknown Source) at java.net.PlainSocketImpl.connect(Unknown Source) at java.net.SocksSocketImpl.connect(Unknown Source) at java.net.Socket.connect(Unknown Source) at sun.security.ssl.SSLSocketImpl.connect(Unknown Source) at com.ibm.db2.jcc.t4.x.run(x.java:250) at java.security.AccessController.doPrivileged(Native Method) at com.ibm.db2.jcc.t4.ac.a(ac.java:422) ... 13 more

For a normal connection it is working fine but for an SSL connection it is giving me this error.

Firewall is also disabled, but still getting this error.

I think it is a port issue. How to resolve this?