Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Trying to connect DB2 through REXX

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
kushal Bothra

New User


Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan

PostPosted: Thu Nov 01, 2012 10:27 am    Post subject: Trying to connect DB2 through REXX
Reply with quote

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
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Nov 01, 2012 10:43 am    Post subject:
Reply with quote

Did you search forum for -107 ?
Back to top
View user's profile Send private message
kushal Bothra

New User


Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan

PostPosted: Thu Nov 01, 2012 12:11 pm    Post subject:
Reply with quote

-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
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Nov 01, 2012 12:28 pm    Post subject:
Reply with quote

What is 'PI000004' ?
Back to top
View user's profile Send private message
kushal Bothra

New User


Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan

PostPosted: Thu Nov 01, 2012 12:37 pm    Post subject:
Reply with quote

'PI000004' is my region

THe actual Db2 query i want to run is DROP VIEW PI000004.PI@PPDI;
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Nov 01, 2012 12:46 pm    Post subject:
Reply with quote

Is 'PI000004' your subsystem id ??

Subsystem id is something what you give in Run or bind cards

Code:
DSN SYSTEM (SSID)
Back to top
View user's profile Send private message
kushal Bothra

New User


Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan

PostPosted: Thu Nov 01, 2012 1:54 pm    Post subject:
Reply with quote

it worked for me by changing PI00004 to BSAO
thanks Pandora icon_biggrin.gif
Back to top
View user's profile Send private message
kushal Bothra

New User


Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan

PostPosted: Thu Nov 01, 2012 2:09 pm    Post subject:
Reply with quote

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 icon_rolleyes.gif


not sure whats wrong in prepare statement

Code'd, but not much help as no indentation.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Thu Nov 01, 2012 3:04 pm    Post subject:
Reply with quote

Why do you refuse to check the return codes from your commands?
Back to top
View user's profile Send private message
kushal Bothra

New User


Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan

PostPosted: Thu Nov 01, 2012 3:09 pm    Post subject:
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu Nov 01, 2012 3:12 pm    Post subject: Reply to: Trying to connect DB2 through REXX
Reply with quote

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
View user's profile Send private message
kushal Bothra

New User


Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan

PostPosted: Thu Nov 01, 2012 3:14 pm    Post subject:
Reply with quote

Is it that you mean to say DROP VIEW statement cannot be executed by rexx icon_surprised.gif
Back to top
View user's profile Send private message
kushal Bothra

New User


Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan

PostPosted: Thu Nov 01, 2012 3:32 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Thu Nov 01, 2012 3:50 pm    Post subject:
Reply with quote

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
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Thu Nov 01, 2012 6:03 pm    Post subject:
Reply with quote

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
View user's profile Send private message
kushal Bothra

New User


Joined: 16 Feb 2011
Posts: 38
Location: Gurgoan

PostPosted: Thu Nov 01, 2012 6:07 pm    Post subject:
Reply with quote

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
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Thu Nov 01, 2012 6:14 pm    Post subject:
Reply with quote

As per my earlier post, EXECUTE IMMEDIATE will work. It is my preferred method for this type of SQL statement.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts What is the command to check MODE of ... rohanthengal CLIST & REXX 6 Fri Nov 18, 2016 1:48 pm
This topic is locked: you cannot edit posts or make replies. Rexx to create GDG by scanning JJ result krish.deepu CLIST & REXX 5 Tue Oct 25, 2016 5:32 pm
No new posts Replace repeated JCL with looping REXX prino CLIST & REXX 3 Thu Oct 13, 2016 5:30 am
This topic is locked: you cannot edit posts or make replies. Need to code a Rexx PGM sundaram.naveen CLIST & REXX 18 Thu Oct 06, 2016 6:45 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us