Db2 table information

Elixir · Posted: Tue Dec 20, 2011 1:16 am

Hello Friends,

I am trying to obtain the information from a DB2 table by providing the Unique key field.

I am able to extract only that many row whose total character count is <= 250. (COL_1,COL_2 ---count is 10)

A_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX') is used.

When I try to obtain the details of additional rows from the table am getting below error after "ADDRESS DSNREXX 'EXECSQL PREPARE S1 FROM :SQLSTMT'"

"ADDRESS DSNREXX 'column fetched variable names"
"+++ S_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX')"
"IRX0030I Error running DB2, line 200: Name or string > 250 characters"

Please let me know how its possible to get all the colums from a table.

Akatsukami · Posted: Tue Dec 20, 2011 1:23 am

That message is coming from Rexx, not DSNREXX or DB2. Notice the unbalanced quotes in your ADDRESS statement.

Elixir · Posted: Tue Dec 20, 2011 1:26 am

i dont think so.

I have just removed one column from the db2 select statement and am able to get the information successfully.

dick scherrer · Posted: Tue Dec 20, 2011 1:34 am

Hello,

don.leahy · Posted: Tue Dec 20, 2011 1:39 am

Use HILITE REXX and the cause of the problem will become obvious.

Elixir · Posted: Tue Dec 20, 2011 1:47 am

SQLSTMT ="SELECT COL_1,COL_2,....." <end>
SQLSTMT1 =",COL_14,..... FROM TABLE"<end>
SQLSTMT2 =" WHERE COL_1 ='
SQLSTMT3 = ""COLVAL"" --> Taken from the user
SQLSTMT = SQLSTMT SQLSTMT1 SQLSTMT2 SQLSTMT3

ADDRESS DSNREXX 'EXECSQL PREPARE S1 FROM :SQLSTMT'
failing at this point if the count > 250 as mentioned above.

Dick S,
removing the column had made the string character count < 250.

line 200 is "ADDRESS DSNREXX 'EXECSQL PREPARE S1 FROM :SQLSTMT'
"

Nic Clouston · Posted: Tue Dec 20, 2011 2:43 pm

You are missing a closing " on SQLSTMT2 but if this just a typo then do not retype things - cut and paste them. And use the code tags to enhance the readability of your code.

Elixir · Posted: Tue Dec 20, 2011 9:16 pm

Elixir · Posted: Tue Dec 20, 2011 9:17 pm

pasted the entire snippet

Nic Clouston · Posted: Tue Dec 20, 2011 9:37 pm

You may have pasted it but you did not use the code tags (hint: highlight the code to be 'coded' and click on the Code button). I am not going to waste time downloading your code to my editor to try and see it properly.

enrico-sorichetti · Posted: Tue Dec 20, 2011 9:42 pm

post edited to add the code tags, anyway
the snippet as posted will never run...
a couple of THEN are missing

Elixir · Posted: Wed Dec 21, 2011 1:31 am

yes..THEN's are the only things missing....

The issue is only with the below because if string length is greater than 250 the IF statements are not getting executed at all.

<code - start>
S_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX')

ADDRESS DSNREXX "CONNECT <db2 test region>"

SQLSTMT ="SELECT COL_1,COL_2,....." <end>
SQLSTMT1 =",COL_14,..... FROM TABLE"<end>
SQLSTMT2 =" WHERE COL_1 ="
SQLSTMT3 = ""COLVAL"" --> Taken from the user
SQLSTMT = SQLSTMT SQLSTMT1 SQLSTMT2 SQLSTMT3

ADDRESS DSNREXX 'EXECSQL PREPARE A1 FROM :SQLSTMT'

<code - end>

after executing the last line of code shown above, IF the total string length in SQLSTMT(SQLSTMT + SQLSTMT1 + SQLSTMT2 + SQLSTMT3) > 250 ,REXX is failing with

"ADDRESS DSNREXX 'column fetched variable names"
"+++ S_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX')"
"IRX0030I Error running DB2, line 200: Name or string > 250 characters"

I feel S_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX') is not able to handle more than 250 characters.

Nic Clouston · Posted: Wed Dec 21, 2011 2:13 am

RXSUBCON has nothing to do with it - it is only making the Rexx/DB2 interface available (if it is not already - did you first check as per the documentation?).

daveporcelan · Posted: Wed Dec 21, 2011 2:24 am

Try coding it like this (notice code tag being used):

Elixir · Posted: Wed Dec 21, 2011 2:59 am

It worked for me.