MVSFORUMS.com :: View topic - Rexx DB2 insert dropping leading zeroes

through

[^/[Print]\]
Goto page 1, 2 Next :| |:
MVSFORUMS.com -> TSO and ISPF

#1: Rexx DB2 insert dropping leading zeroes Author: misi01, Location: Stockholm, Sweden

Posted: Tue Jul 15, 2014 1:00 am
—
I'm having the following problem. My rexx script is supposed to insert data into a table (no problem there), but ....

One of the fields in the DB2 table is defined as character and the value to be inserted is 030462908. Up until the actual insert, I can see that the variable contains the leading zero, but the actual inserted row has lost the zero (I can potentially understand where/why). Is there a way of keeping the zero other than (maybe) something like prefixing the value with an X and then doing the insert using SUBSTR ?

Any ideas, anyone ?

#2: Okay, I figured out A way Author: misi01, Location: Stockholm, Sweden

Posted: Tue Jul 15, 2014 1:34 am
—
but is this solution the only way to get round it. Here's my original solution

Code:

and

Code:

"EXECSQL EXECUTE S2 USING :aridfr ,:pdaridfr",
",:pdarsecid ,:today ",
",:tcnldbkey ,:moid ,:ts"

Like I mentioned, the value in pdaridfr kept on losing any leading zeros.
Here's the other version (that works)

Code:

insertstmt = "INSERT INTO xxxx.xxxxxxxx ",
"(",
" ARIDFR",
",PDARIDFR",
",SRCESOFTWAREIDFR",
",PDARSECID",
",PDARSECIDTP",
",PDARLCSTATUS",
",PDARLCSTATUSDT",
",PRODUCTNUMBER",
",PDIDFR",
",PDSECID",
",PDSECIDTP",
",PDARTP",
",TCNLDBKEY",
",MOID",
",TMSTPUPDTROW",
") ",
"VALUES",
"(",
" '"strip(aridfr,'T')"'",
",'"strip(pdaridfr,'T')"'",
",'1001'",
",'"strip(pdarsecid,'T')"'",
",'1'",
",'7'",
",'"today"'",
",'10'",
",'20'",
",'30'",
",'3'",
",'23'",
",'"tcnldbkey"'",
",'"moid"'",
",'"ts"'",
")"
address dsnrexx "execsql prepare s1 from :insertstmt"
if sqlcode <> 0 then
do
rc = db2err_rpt()
rc = disconnect_from_db2t()
exit 1
end

address dsnrexx "execsql execute s1 "

Does keeping leading zeros preclude the first variation ??

#3: Author: kolusu, Location: San Jose

Posted: Tue Jul 15, 2014 1:54 am
—
misi01,

try

Code:

right(pdarifdr,N,'0')

N = length of the column

#4: Author: misi01, Location: Stockholm, Sweden

Posted: Tue Jul 15, 2014 3:10 am
—
Don't think that's what I'm after. In this case, the column is 32 characters, so your suggestion would fill it with loads of leading zeros, NOT what I want.
The value in PDARIDFR already contains what I want in the final DB2 column. It's just that REXX (?) sees it as a numeric value and automatically chops the leading zero, not what I want either.

#5: Author: kolusu, Location: San Jose

Posted: Tue Jul 15, 2014 10:39 am
—

misi01 wrote:

Don't think that's what I'm after. In this case, the column is 32 characters, so your suggestion would fill it with loads of leading zeros, NOT what I want.
The value in PDARIDFR already contains what I want in the final DB2 column. It's just that REXX (?) sees it as a numeric value and automatically chops the leading zero, not what I want either.

misi01,

You can limit the padding to the length of what you want

Code:

RIGHT(PDARIFDR,LENGTH(PDARIFDR),'0')

#6: I think we're talking at x-purposes here Author: misi01, Location: Stockholm, Sweden

Posted: Wed Jul 16, 2014 4:19 am
—
Assume the value in pdaridfr is 123 and I want it to be 4 characters with leading zeroes. I could do what you suggest above and get 0123. Only trouble with that is that I already have a value of 0123 in pdaridfr, so I'm not sure why I would need to use right and pad with zeroes.

My problem seems to be that Rexx, when it's running the first example I gave above,strips the leading zero. What I need in the first case is a way of expressing the Rexx/DB2 call so that the value in pdaridfr is passed to DB2 as-is.
I tried with variations of the code below (note the single quotes around the variable pdaridfr) but none of those I tried helped.

Code:

"EXECSQL EXECUTE S2 USING :aridfr ,:'"pdaridfr"'",
",:pdarsecid ,:today ",
",:tcnldbkey ,:moid ,:ts"

#7: Re: I think we're talking at x-purposes here Author: kolusu, Location: San Jose

Posted: Wed Jul 16, 2014 11:37 am
—

misi01 wrote:

Assume the value in pdaridfr is 123 and I want it to be 4 characters with leading zeroes. I could do what you suggest above and get 0123. Only trouble with that is that I already have a value of 0123 in pdaridfr, so I'm not sure why I would need to use right and pad with zeroes.

misi01,

I understand that you ALREADY have the leading zero, but using the RIGHT makes REXX NOT to STRIP the leading zero as it's job is to PAD the zeroes. Since you already have the zero it is going to retain the value AS-IS.

#8: Author: RoyKMathur,

Posted: Wed Jul 16, 2014 12:37 pm
—
misi01, I just wrote a rexx to execute an insert with a leading zero and it worked fine. Is it possible that what is displaying the results of the insert is removing the leading zero?

#9: Author: misi01, Location: Stockholm, Sweden

Posted: Thu Jul 17, 2014 1:15 am
—
Roy. Did you use the syntax from the first example? If so, what did your definition for the inserted column look like?

#10: In answer to both suggestions Author: misi01, Location: Stockholm, Sweden

Posted: Thu Jul 17, 2014 1:34 am
—
Roy, I'm using SPUFI. Other character columns with leading zeros are shown with those leading zeros.

Kolusu. My problem seems to be how to use the rexx RIGHT for the first syntax example. Here's a couple of attempts. First a recap of the original code

Code:

pdarsecid = pdarsecid + 1
pdarsecid = right(pdarsecid,9,'0')
address dsnrexx ,
"EXECSQL EXECUTE S2 USING :aridfr ,:pdaridfr",
",:pdarsecid ,:today ",
",:tcnldbkey ,:moid ,:ts"

(PDARSECID is giving the same problem). Those leading zeros are stripped on the insert
(same problem as pdaridfr).
If I try something like

Code:

pdarsecid = pdarsecid + 1
pdarsecid = right(pdarsecid,9,'0')
address dsnrexx ,
"EXECSQL EXECUTE S2 USING :aridfr ,:pdaridfr",
pdarsecid",:today ",
",:tcnldbkey ,:moid ,:ts"

I get the following

Quote:

SQLSTATE = 42601 SQLERRD = 0,0,0,0,0,0 SQLERRP = DSNTZEXE SQLERRMC =
000000001.<HOST-VARIABLE> .° DSNT408I SQLCODE = -104, ERROR: ILLEGAL
SYMBOL "". SOME SYMBOLS THAT MIGHT BE LEGAL ARE:
DSNT415I SQLERRP = SQL PROCEDURE DETECTING ERROR

(000000001 is the Rexx value of pdarsecid)

So my question remains, how do I express the EXECUTE S2 statement such that leading zeros aren't removed for character fields.

At the end of the day, if the only option is to use the second option from my first append, fine. Then I'll stop wasting time trying to get something to work that never will.

#11: Author: RoyKMathur,

Posted: Thu Jul 17, 2014 5:08 am
—
misi01,

Actually even though I read all the posts...twice...I still don't quite understand everything you're saying/showing. So I just coded it the way I would. This is a primitive example, where I have hard-coded the number and have to change it each time I execute the program; otherwise I'll get a -803. Here is my code.

Code:

/* REXX - test inserting a number with a leading zero into a char field */

/*-------------------------------------------------------------------
* Ensure the DB2 interface is active.
*-----------------------------------------------------------------*/
ADDRESS TSO "SUBCOM DSNREXX"

s_rc = rc

if s_rc <> 0 then do
s_rc = RXSUBCOM('ADD','DSNREXX','DSNREXX')

if s_rc <> 0 then do
say '==> Error 1: Activating REXX/DB2 Interface RXSUBCOM, rc='s_
exit 16
end
end

address dsnrexx "CONNECT DB2U"

s_rc = rc

if s_rc <> 0 then do
say '==> Error 2: Connecting REXX to DB2 Subsystem DB2U, rc='s_rc
exit 16
end

/*-------------------------------------------------------------------
* Insert a row
*-----------------------------------------------------------------*/
s_key = '01234568'
s_key = s_key + 1
s_key = right(s_key,8,'0')

s_sql = "INSERT INTO DB2ROYU.BIL_ACT_INQUIRY",
" (BIL_ACCOUNT_ID",
" ,BIL_ACY_TYPE_CD",
" ,BIL_NXT_ACY_DT",
" )",
" VALUES('"s_key"'",
" ,'X'",
" ,'01/01/2014'",
" )"

ADDRESS DSNREXX "EXECSQL " s_sql

say 'rc = 'rc
say 'sqlcode = 'sqlcode

exit 0

Here is the table's definition:

Code:

COLUMN NAME DATATYPE LENGTH
------------------ -------- ------
BIL_ACCOUNT_ID CHAR 8
BIL_ACY_TYPE_CD CHAR 3
BIL_NXT_ACY_DT DATE 4

I ran the rexx program three times for three different numbers. Here is the SPUFI results:

Code:

SELECT * FROM DB2ROYU.BIL_ACT_INQUIRY
WHERE BIL_ACCOUNT_ID LIKE '0123456%'
WITH UR;
---------+---------+---------+---------+-------
BIL_ACCOUNT_ID BIL_ACY_TYPE_CD BIL_NXT_ACY_DT
---------+---------+---------+---------+-------
01234567 X 01/01/2014
01234568 X 01/01/2014
01234569 X 01/01/2014

#12: Sorry Roy ... Author: misi01, Location: Stockholm, Sweden

Posted: Thu Jul 17, 2014 7:58 am
—
all you're doing is confirming what I wrote at the top of the topic. Your (working) version is the same as my working one (the second example). Try the following instead

Code:

insertstmt = "INSERT INTO DB2ROYU.BIL_ACT_INQUIRY ",
"(",
"BIL_ACCOUNT_ID",
" ,BIL_ACY_TYPE_CD",
" ,BIL_NXT_ACY_DT",
") ",
"VALUES",
"(",
" ?",
",?",
",?",
")"
address dsnrexx "EXECSQL PREPARE S2 FROM :insertstmt"

and then

Code:

"EXECSQL EXECUTE S2 USING :s_key ,:BIL_ACY_TYPE_CD",
",:BIL_NXT_ACY_DT "

where, as in your example, s_key contains leading zeros.
See if you get those in your DB2 column

#13: Author: RoyKMathur,

Posted: Thu Jul 17, 2014 9:39 am
—
But why are you preparing the statement? If it works the other way, why not use it? I suppose you're trying to not flood the dynamic cache.

I tried your example and sure enough I get the same error you're getting. Seems like an IBM DB2 REXX bug.

The DB2 manual says that alternately you can use the DB2 ODBC functions (SQLPrepare and SQLExecute). I've never used them and don't know anything about them.

Quote:

The statement can also be prepared by calling the DB2 ODBC SQLPrepare function and then executed by calling the DB2 ODBC SQLExecute function. In both cases, the application does not contain an embedded PREPARE or EXECUTE statement. You can execute the statement, without preparation, by passing the statement to the DB2 ODBC SQLExecDirect function. DB2 ODBC Guide and Reference describes the APIs supported with this interface.

[/code]

#14: Author: misi01, Location: Stockholm, Sweden

Posted: Thu Jul 17, 2014 10:32 am
—
Roy. I have no problem in using the second variation. It's more a learning curve really. Anyone else who sees this topic will now see the problem and solution.

#15: Author: RoyKMathur,

Posted: Thu Jul 17, 2014 12:37 pm
—
Okay, so I discussed this with one of my co-workers and she immediately said that makes sense, that the Execute Immediate would work but the Prepare wouldn't because of the BIND Validate(Run|Bind) parameter. Within seconds she'd looked it up and confirmed. Then she looked in a different DB2 manual than I had looked at earlier...the right manual...the DB2 10 for z/OS Application Programming and SQL Guide and found in Chapter 9 in bookmark "Ensuring that DB2 correctly interprets character input data in REXX programs" it shows an example where you first have to define the SQLDA. It helps to be a real DBA. One day maybe I will be.

The following works.

Code:

/* REXX - test inserting a number with a leading zero into a char field */

/*-------------------------------------------------------------------
* Ensure the DB2 interface is active.
*-----------------------------------------------------------------*/
ADDRESS TSO "SUBCOM DSNREXX"

s_rc = rc

if s_rc <> 0 then do
s_rc = RXSUBCOM('ADD','DSNREXX','DSNREXX')

if s_rc <> 0 then do
say '==> Error 1: Activating REXX/DB2 Interface RXSUBCOM, rc='s_rc
exit 16
end
end

address dsnrexx "CONNECT DB2U"

s_rc = rc

if s_rc <> 0 then do
say '==> Error 2: Connecting REXX to DB2 Subsystem DB2U, rc='s_rc
exit 16
end

/*-------------------------------------------------------------------
* Insert a row
*-----------------------------------------------------------------*/
s_key = '01234571'
s_key = s_key + 1
s_key = right(s_key,8,'0')

s_cd = 'X'
s_dt = '01/01/2014'

INSQLDA.SQLD = 3
INSQLDA.1.SQLTYPE = 453
INSQLDA.1.SQLLEN = 8
INSQLDA.1.SQLDATA = s_key
INSQLDA.1.SQLIND = 0
INSQLDA.2.SQLTYPE = 453
INSQLDA.2.SQLLEN = 3
INSQLDA.2.SQLDATA = s_cd
INSQLDA.2.SQLIND = 0
INSQLDA.3.SQLTYPE = 385
INSQLDA.3.SQLLEN = 10
INSQLDA.3.SQLDATA = s_dt
INSQLDA.3.SQLIND = 0

s_sql = "INSERT INTO DB2ROYU.BIL_ACT_INQUIRY",
" (BIL_ACCOUNT_ID",
" ,BIL_ACY_TYPE_CD",
" ,BIL_NXT_ACY_DT",
" )",
" VALUES(?",
" ,?",
" ,?",
" )"

ADDRESS DSNREXX "EXECSQL PREPARE S2 FROM :s_sql"

say 'rc = 'rc
say 'sqlcode = 'sqlcode

ADDRESS DSNREXX "EXECSQL EXECUTE S2 USING DESCRIPTOR :INSQLDA"

say 'rc = 'rc
say 'sqlcode = 'sqlcode

exit 0

MVSFORUMS.com -> TSO and ISPF

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Goto page 1, 2 Next :| |: Page 1 of 2