Joined: 02 Dec 2002 Posts: 629 Topics: 176 Location: Stockholm, Sweden
Posted: Tue Jul 15, 2014 1:00 am Post subject: Rexx DB2 insert dropping leading zeroes
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 ?
Joined: 02 Dec 2002 Posts: 629 Topics: 176 Location: Stockholm, Sweden
Posted: Tue Jul 15, 2014 3:10 am Post subject:
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. _________________ Michael
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Tue Jul 15, 2014 10:39 am Post subject:
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
Joined: 02 Dec 2002 Posts: 629 Topics: 176 Location: Stockholm, Sweden
Posted: Wed Jul 16, 2014 4:19 am Post subject: I think we're talking at x-purposes here
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.
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Wed Jul 16, 2014 11:37 am Post subject: Re: I think we're talking at x-purposes here
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. _________________ Kolusu
www.linkedin.com/in/kolusu
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?
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. _________________ Michael
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
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
Joined: 02 Dec 2002 Posts: 629 Topics: 176 Location: Stockholm, Sweden
Posted: Thu Jul 17, 2014 7:58 am Post subject: Sorry Roy ...
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
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.
Joined: 02 Dec 2002 Posts: 629 Topics: 176 Location: Stockholm, Sweden
Posted: Thu Jul 17, 2014 10:32 am Post subject:
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. _________________ Michael
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
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
