|
View previous topic :: View next topic
|
| Author |
Message |
kushal Bothra
New User
.jpg)
Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan
|
|
|
|
Hi All,
I am Trying to connect DB2 through REXX with forllowing peice of code
***************************** Top of Data ************
/*REXX*/
USER = SYSVAR('SYSUID')
SSID = 'PI000004'
ADDRESS TSO "SUBCOM DSNREXX"
IF RC THEN
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT "SSID
IF SQLCODE = 0 THEN
SAY 'ACCT DELETED SUCCESSFULLY'
ELSE
SAY SQLCODE
SAY 'ACCT SUCCESSFULLY'
EXIT
**************************** Bottom of Data ****************************
Execution ended with SQL code -107 resuls are as
-107
ACCT SUCCESSFULLY
***
Please suggest what can be possible resolution.[/img] |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Did you search forum for -107 ? |
|
| Back to top |
|
|
kushal Bothra
New User
Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan
|
|
|
|
-107
THE NAME name IS TOO LONG. MAXIMUM ALLOWABLE SIZE IS size
I am not sure if i am i am getting error because of this reason |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| What is 'PI000004' ? |
|
| Back to top |
|
|
kushal Bothra
New User
Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan
|
|
|
|
'PI000004' is my region
THe actual Db2 query i want to run is DROP VIEW PI000004.PI@PPDI; |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Is 'PI000004' your subsystem id ??
Subsystem id is something what you give in Run or bind cards
|
|
| Back to top |
|
|
kushal Bothra
New User
Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan
|
|
|
|
it worked for me by changing PI00004 to BSAO
thanks Pandora  |
|
| Back to top |
|
|
kushal Bothra
New User
Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan
|
|
|
|
I writing a simple drop view command
| Code: |
/*REXX*/
START:
USER = SYSVAR('SYSUID')
SSID = 'BDS0'
ADDRESS TSO "SUBCOM DSNREXX"
IF RC THEN
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT "SSID
SAY SQLCODE
QUERYSQL = "DROP VIEW PI000004.PI@PPDI"
ADDRESS DSNREXX "EXECSQL PREPARE S2 FROM :QUERYSQL"
ADDRESS DSNREXX "EXECSQL EXECUTE S2"
SAY SQLCODE
IF SQLCODE = 0 THEN DO
SAY 'ACCT DELETED SUCCESSFULLY'
END
EXIT
|
The return code I am getting is -518 --> THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT 
not sure whats wrong in prepare statement
Code'd, but not much help as no indentation. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| Why do you refuse to check the return codes from your commands? |
|
| Back to top |
|
|
kushal Bothra
New User
Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan
|
|
|
|
Hi Akatsukami,
When I am doing
QUERYSQL = "DROP VIEW PI000004.PI@PPDI"
ADDRESS DSNREXX "EXECSQL PREPARE S2 FROM :QUERYSQL"
ADDRESS DSNREXX "EXECSQL EXECUTE S2"
SAY SQLCODE
The SQLCODE i'm getting for SAY SQLCODE is
-518
I am not sure the reason for -518 |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
read the DB2/REXX manuals for the DB2 statements that can be processed thru the rexx interface
read the DB2 manuals for the statements that can be dynamically executed
( prepare and friends ) |
|
| Back to top |
|
|
kushal Bothra
New User
Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan
|
|
|
|
Is it that you mean to say DROP VIEW statement cannot be executed by rexx  |
|
| Back to top |
|
|
kushal Bothra
New User
Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan
|
|
|
|
DB2® REXX Language Support supports all dynamic SQL statements and the following static SQL statements:
•CALL
•CLOSE
•CONNECT
•DECLARE CURSOR
•DESCRIBE prepared statement or table
•DESCRIBE CURSOR
•DESCRIBE INPUT
•DESCRIBE PROCEDURE
•EXECUTE
•EXECUTE IMMEDIATE
•FETCH
•OPEN
•PREPARE
•RELEASE connection
•SET CONNECTION
•SET CURRENT PACKAGE PATH
•SET CURRENT PACKAGESET
•SET host-variable = CURRENT DATE
•SET host-variable = CURRENT DEGREE
•SET host-variable = CURRENT MEMBER
•SET host-variable = CURRENT PACKAGESET
•SET host-variable = CURRENT PATH
•SET host-variable = CURRENT SERVER
•SET host-variable = CURRENT SQLID
•SET host-variable = CURRENT TIME
•SET host-variable = CURRENT TIMESTAMP
•SET host-variable = CURRENT TIMEZONE |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| kushal Bothra wrote: |
When I am doing
QUERYSQL = "DROP VIEW PI000004.PI@PPDI"
ADDRESS DSNREXX "EXECSQL PREPARE S2 FROM :QUERYSQL"
ADDRESS DSNREXX "EXECSQL EXECUTE S2"
SAY SQLCODE
The SQLCODE i'm getting for SAY SQLCODE is
-518
I am not sure the reason for -518 |
Well, why not try something like the following:
| Code: |
QUERYSQL = "DROP VIEW PI000004.PI@PPDI"
ADDRESS DSNREXX "EXECSQL PREPARE S2 FROM :QUERYSQL"
if (rc¬=0) then do
say "RC from DSNREXX =" rc
exit 20
end
ADDRESS DSNREXX "EXECSQL EXECUTE S2"
if (sqlcode<0) then do
address link "RXSQLCA"
say SQLERRMSG
exit 20 |
Search the CLIST & REXX forum for RXSQLCA. |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
Try "EXECUTE IMMEDIATE". It performs a PREPARE for you under the covers. It only works for non-SELECT SQL statements.
| Code: |
| ADDRESS DSNREXX "EXECSQL EXECUTE IMMEDIATE :SQLSTMT" |
Edit: added example |
|
| Back to top |
|
|
kushal Bothra
New User
Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan
|
|
|
|
Hope it will work..
so as per your suggestion I dont need to write
EXECSQL PREPARE S2 FROM :QUERYSQL
On place of
ADDRESS DSNREXX "EXECSQL PREPARE S2 FROM :QUERYSQL" ADDRESS DSNREXX "EXECSQL EXECUTE S2"
I can write
ADDRESS DSNREXX "EXECSQL EXECUTE IMMEDIATE :QUERYSQL"
Please correct me if I am wrong |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| As per my earlier post, EXECUTE IMMEDIATE will work. It is my preferred method for this type of SQL statement. |
|
| Back to top |
|
|
|
|
