| View previous topic :: View next topic |
| Author |
Message |
ramu_mohan21
Beginner
Joined: 29 Jun 2004
Posts: 106
Topics: 41
Location: Bangalore, INDIA
|
 Posted: Fri May 18, 2007 2:42 pm Post subject: -805 in REXX DB2 Program |
 |
|
HI Board,
[code:1:0b03ce78ba]SUBSYS = DB2T
SQLSTMT1 = "SELECT SAL INTO :EMPSAL FROM ABC.EMPLOYEE",
" WHERE EMPNO = '123456'"
ADDRESS TSO "SUBCOM DSNREXX"
IF RC THEN
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT" SUBSYS
IF SQLCODE
_________________
Best Regards,
----------------
Rammohan Pabba
Software Engineer |
|
| Back to top |
|
 |
ofer71
Intermediate
Joined: 12 Feb 2003
Posts: 358
Topics: 4
Location: Israel
|
| Posted: Sat May 19, 2007 10:28 pm Post subject: |
|
|
I believe you can solve the problem by looiking at the fine manual.
O.
________
ultimate fighter
Last edited by ofer71 on Sat Feb 05, 2011 12:00 pm; edited 1 time in total |
|
| Back to top |
|
|
Nic Clouston
Advanced
Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home
|
| Posted: Sun May 20, 2007 3:43 am Post subject: |
|
|
you do not SELECT ... INTO ...
_________________
Utility and Program control cards are NOT, repeat NOT, JCL. |
|
| Back to top |
|
|
ramu_mohan21
Beginner
Joined: 29 Jun 2004
Posts: 106
Topics: 41
Location: Bangalore, INDIA
|
| Posted: Sun May 20, 2007 3:57 am Post subject: |
|
|
HI Nic,
My requirement is to get the single column value into a host variable. EMPNO is the primary key on Employee table. Hence I do not want to use cursor. How my requirement can be satisfied?
_________________
Best Regards,
----------------
Rammohan Pabba
Software Engineer |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
|
| Back to top |
|
|
ramu_mohan21
Beginner
Joined: 29 Jun 2004
Posts: 106
Topics: 41
Location: Bangalore, INDIA
|
| Posted: Wed May 23, 2007 9:57 am Post subject: |
|
|
HI,
For the above code please anybody can delcare SQLDA declarations. I'm getting confusion in declaring that. I refered manuals and this board also. Still unable to do so. What are the fields are must and should while declaring SQLDA?
_________________
Best Regards,
----------------
Rammohan Pabba
Software Engineer |
|
| Back to top |
|
|
Nic Clouston
Advanced
Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home
|
| Posted: Wed May 23, 2007 10:10 am Post subject: |
|
|
In Rexx you do not declare anything - you just use it. DSNRexx will build SQLDA and you just reference it.
Here is my very first Rexx/DB2 attempt - slightly updated to show how to use SQLDA or rexx variables:
| Code: |
/*------------------------------------------------------------ REXX -*/
'CLEAR'
/* Set DB2 sub-system ID */
ssid = 'DB2T';
/* Set SQL statement */
sqlstmt = ,
"SELECT ",
"r_nsc,r_account,r_limitamt,r_priority",
"FROM xxx.xxx_xxxx_xxxxx";
/*------------------------------
Column Name Data Type Length
------------ --------- ------
R_NSC CHAR 6
R_ACCOUNT CHAR 8
R_LIMITAMT DECIMAL 13
R_PRIORITY INTEGER 4
-------------------------------*/
/* Is DSNREXX available? */
/* DSNREXX is the API */
'SUBCOM DSNREXX';
If rc, /* rc from SUBCOM is 0 or 1*/
Then Do; /* 0 - is there, 1 is not */
s_rc = RXSUBCOM('ADD','DSNREXX','DSNREXX');
If s_rc <> 0,
Then Do;
zedsmsg = 'RXSUBCOM rc = 's_rc
zedlmsg = 'RXSUBCOM failed to add the DSNREXX environment'
Address ISPEXEC 'SETMSG MSG(ISRZ001)';
Exit /* exit directly */
End;
remove_dsnrexx = 1
End;
Else remove_dsnrexx = 0
Address DSNREXX; /* All external commands go to DSNREXX unless spec. */
/* Connect to DB2 sub-system */
'CONNECT 'ssid;
If rc <> 0,
Then Do;
zedsmsg = 'CONNECT rc = 'rc
zedlmsg = 'Failed to connect to 'ssid' Return code = 'rc
Address ISPEXEC 'SETMSG MSG(ISRZ001)';
Signal door1; /* exit after remove */
End;
/* Declare cursor - always required */
'EXECSQL DECLARE C1 CURSOR FOR S1'; /* C1 for S1 etc */
If rc <> 0,
Then Do;
zedsmsg = 'DECLARE code = 'sqlcode;
zedlmsg = 'Failed to declare cursor C1. SQLCODE = 'sqlcode
Address ISPEXEC 'SETMSG MSG(ISRZ001)';
Signal door; /* exit after disconnect & remove */
End;
/* Prepare statement */
'EXECSQL PREPARE S1 INTO :outsqlda FROM :sqlstmt';
If rc <> 0,
Then Do;
zedsmsg = 'PREPARE code = 'sqlcode;
zedlmsg = 'SQLERRMC from PREPARE = 'sqlerrmc;
Address ISPEXEC 'SETMSG MSG(ISRZ001)';
Signal door; /* exit after disconnect & remove */
End;
/* Open cursor */
'EXECSQL OPEN C1';
If sqlcode <> 0,
Then Do;
zedsmsg = 'SQLCODE from OPEN = 'sqlcode;
zedlmsg = 'SQLERRMC from OPEN = 'sqlerrmc;
Address ISPEXEC 'SETMSG MSG(ISRZ001)';
Signal door; /* exit after disconnect & remove */
End;
/* Fetch cursor */
'EXECSQL FETCH C1 INTO :r_nsc, :r_account, :r_limitamt, :r_priority'
/* Can also use: */
/* 'EXECSQL FETCH C1 USING DESCRIPTOR :outsqlda'; */
/**/
Do While sqlcode = 0
/* Process data */
/* use these lines if data was obtained by using:
'EXECSQL FETCH C1 INTO :r_nsc, :r_account, :r_limitamt, :r_priority'
*/
oline1 = 'NSC = 'r_nsc
oline2 = 'Account = 'r_account
oline3 = 'LimitAmt = 'r_limitamt
oline4 = 'Priority = 'r_priority
/* use these lines if data was obtained by using:
'EXECSQL FETCH C1 USING DESCRIPTOR :outsqlda'
oline1 = 'NSC = 'outsqlda.1.sqldata;
oline2 = 'Account = 'outsqlda.2.sqldata;
oline3 = 'LimitAmt = 'outsqlda.3.sqldata;
oline4 = 'Priority = 'outsqlda.4.sqldata; */
/* Do i = 1 to OUTSQLDA.SQLD
SAY " > COLUMN NUMBER: " I
SAY " COLUMN NAME: " OUTSQLDA.I.SQLNAME
SAY " COLUMN TYPE: " OUTSQLDA.I.SQLTYPE
SAY " COLUMN VALUE: " OUTSQLDA.I.SQLDATA
End */
Address ISPEXEC 'DISPLAY PANEL(REXXDB2)'
'EXECSQL FETCH C1 INTO :r_nsc, :r_account, :r_limitamt, :r_priority'
/* 'EXECSQL FETCH C1 USING DESCRIPTOR :outsqlda'; */
End
If sqlcode <> 100,
Then Do;
zedsmsg = 'SQLCODE from FETCH = 'sqlcode;
zedlmsg = 'SQLERRMC from FETCH = 'sqlerrmc;
Address ISPEXEC 'SETMSG MSG(ISRZ001)';
Signal door; /* exit after disconnect & remove */
End;
/* Close cursor */
'EXECSQL CLOSE C1';
If rc <> 0,
Then Do;
zedsmsg = 'SQLCODE from CLOSE = 'sqlcode;
zedlmsg = 'SQLERRMC from CLOSE = 'sqlerrmc;
Address ISPEXEC 'SETMSG MSG(ISRZ001)';
Signal door; /* exit after disconnect & remove */
End;
/* Cleanup and exit */
door:
/* Disconnect from DB2 */
'DISCONNECT';
door1:
/* If we added DSNREXX then we must remove it. */
If remove_dsnrexx ,
Then s_rc = RXSUBCOM('DELETE','DSNREXX','DSNREXX');
door2: /* No cleanup required/done*/
Exit
|
In this example I have commented out the SQLDA usage but I have left it here as this is a reference exec for myself - saves me re-inventing the wheel. (In fact, the exec came from someone else but as something more complex as it was doing a real-life task)
_________________
Utility and Program control cards are NOT, repeat NOT, JCL. |
|
| Back to top |
|
|
ramu_mohan21
Beginner
Joined: 29 Jun 2004
Posts: 106
Topics: 41
Location: Bangalore, INDIA
|
| Posted: Wed May 23, 2007 11:30 am Post subject: |
|
|
[code:1:a568c8daee]/*** REXX ***/
SUBSYS = 'DB2T'
SQLSTMT = ,
"SELECT",
"DEPT",
"FROM XXX.EMPLOYEE"
SAY 'SQL Statement:' SQLSTMT
ADDRESS TSO "SUBCOM DSNREXX"
IF RC THEN
Do
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
SAY 'DB2 SUBCOM DSNREXX Added'
End
ADDRESS DSNREXX "CONNECT" SUBSYS
IF SQLCODE
_________________
Best Regards,
----------------
Rammohan Pabba
Software Engineer |
|
| Back to top |
|
|
CraigG
Intermediate
Joined: 02 May 2007
Posts: 202
Topics: 0
Location: Viginia, USA
|
| Posted: Wed May 23, 2007 11:53 am Post subject: |
|
|
| It looks to me like you need some spaces in SQLSTMT after SELECT and DEPT. I think you are trying to prepare "SELECTDEPTFROM XXX.EMPLOYEE" instead of "SELECT DEPT FROM XXX.EMPLOYEE" |
|
| Back to top |
|
|
Nic Clouston
Advanced
Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home
|
| Posted: Wed May 23, 2007 12:05 pm Post subject: |
|
|
It LOOKS ok but a couple of questiosn - ok 4:
1 - are you running this in batch or interactively? I do not think I have tried to run in batch so I cannot help there.
2 - have you got the correct able name for the SSID?
3 - have you got the correct SSID!?
4 - have you got the correct column name?
Please do not just yes they are all correct - double check. Go into SPUFI - is it using DB2T as its ssid? If it is, cut and paste the query from your exec into your spufi input file - don't just re-type it.
As I say - I cannot see anything obvious so we are clutching at straws!
_________________
Utility and Program control cards are NOT, repeat NOT, JCL. |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Wed May 23, 2007 1:40 pm Post subject: |
|
|
ramu_mohan21,
The FETCH statement is possibly in error; I would suggest this:
| Code: |
EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA
|
instead of this:
| Code: |
EXECSQL FETCH C1 INTO :DEP
|
since your PREPARE was this:
| Code: |
EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :SQLSTMT
|
instead of this:
| Code: |
EXECSQL PREPARE S1 FROM :SQLSTMT
|
and your open was this:
instead of this:
| Code: |
EXECSQL OPEN C1 USING :DEP
|
If you were to go thru Nic's example or read the link that I provided, you would have found your error.
The following is a typical dbz diatribe about unreadable code. Ignore if you wish.
I realize that REXX lends itself to straight down spaghetti code, but you can see that your REXX is a little difficult to follow. Error routines are one off; instead of coding all the error displays in the primary code, use[code:1:bc892f7bd8]
IF SQLCODE
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
videlord
Beginner
Joined: 09 Dec 2004
Posts: 147
Topics: 19
|
| Posted: Wed May 23, 2007 10:44 pm Post subject: |
|
|
usually, -805 in DB2 for z/OS is DBRM/PACKAGE not found/match.
change SQLERRM to SQLERRMC in your SQLCA routine as following
| Code: |
SAY 'SQLERRMC =' SQLERRMC |
need more information about the -805 error |
|
| Back to top |
|
|
Nic Clouston
Advanced
Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home
|
| Posted: Thu May 24, 2007 2:37 am Post subject: |
|
|
Darn Dick, I was just thinking (OK, last night I was thinking) that I should revamp that error process.
_________________
Utility and Program control cards are NOT, repeat NOT, JCL. |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Thu May 24, 2007 2:38 am Post subject: |
|
|
videlord,
| Quote: |
usually, -805 in DB2 for z/OS is DBRM/PACKAGE not found/match
|
exactly. But this is a dynamic bind which means the package/dbrm/plan not found is due to bad syntax in a previous CALL to DSNREXX. No need to search for missing package/dbrm/plan: OP needs to correct the syntax in his REXX script.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
Nic Clouston
Advanced
Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home
|
| Posted: Thu May 24, 2007 4:40 am Post subject: |
|
|
Well, I revamped my error processing and reran and got a PREPARE error! This was a -2xx and SQLERRMC returned me A_NSC. Now, this error is NOT in the script I posted cos I spotted it as I was cutting and pasting and fixed it - but forg to to fix the source! The error? A_NSC is not the column name - it is R_NSC as posted above. So we know that it is not complaining about a duff column name - yet.
CraigG: when Rexx concatenates by abuttment, as in this case, it puts a blank between the parts - you need to use || to avoid having blanks.
Rammohan: Dick is correct - your FETCH needs to be brought into line but that is an error waiting to happen - not the cause - unless Rexx is reading the entire script before executing the PREPARE and is passing other data to DSNREXX - seems unlikely. However, because my exec has 2 methods in it - albeit one is commented out - it does need careful reading. The HI REXX edit command helps!
I still think the issue revolves around the ssid and tablename - is that tablename on that ssid?
_________________
Utility and Program control cards are NOT, repeat NOT, JCL. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
