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
 

 

REXX- delete query in REXX
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
pshongal

New User


Joined: 14 Jun 2012
Posts: 70
Location: India

PostPosted: Wed Jun 27, 2012 4:09 pm    Post subject: REXX- delete query in REXX
Reply with quote

I would like to delete multiple rows in the DB2 table using REXX. I have given GU interface to enter a/c#s. Rexx pgm should delete those from DB. I have below rexx pgm. It ends normally when executed, row is not deleting.

Code:
/*REXX*/
START:
/*
USER = SYSVAR('SYSUID')
"ISPEXEC DISPLAY PANEL(DELEMSCR)"
Message = ' '
If RC ¬= 0 Then
Do
exit
End
If RG = 'E1' Then
SSID = 'DB2D'
Else
SSID = 'DB2T'
ADDRESS TSO "SUBCOM DSNREXX"
IF RC THEN
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT "SSID
QUERYSQL = "DELETE FROM VMUSHE.REL_AD_USE WHERE "||,
"table_name = '"||AC1||"'"||" AND ab_c = NULL"
SAY 'DELETING 'AC1
SAY QUERYSQL
ADDRESS DSNREXX "EXECSQL EXECUTE S1 FROM QUERYSQL"
IF SQLCODE = 0 THEN DO
SAY 'ACCT DELETED SUCCESSFULLY'
END
EXIT
Back to top
View user's profile Send private message

Naish

New User


Joined: 07 Dec 2006
Posts: 82
Location: UK

PostPosted: Wed Jun 27, 2012 4:13 pm    Post subject:
Reply with quote

I am not a REXX guy. But, I feel you missed COMMITTING it...

Also, why did you post again???
Back to top
View user's profile Send private message
pshongal

New User


Joined: 14 Jun 2012
Posts: 70
Location: India

PostPosted: Wed Jun 27, 2012 4:40 pm    Post subject:
Reply with quote

@Naish-
[Also, why did you post again???]

Since moderator had put my query in DB2 forum, I had to post again...
[/quote]
Back to top
View user's profile Send private message
Naish

New User


Joined: 07 Dec 2006
Posts: 82
Location: UK

PostPosted: Wed Jun 27, 2012 4:48 pm    Post subject:
Reply with quote

If you had problems, you may have questioned it on that very post.

Let the moderator reply now...

Also, did you try what was pointed out to you?
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


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

PostPosted: Wed Jun 27, 2012 4:55 pm    Post subject:
Reply with quote

pshongal wrote:
@Naish-
[Also, why did you post again???]

Since moderator had put my query in DB2 forum, I had to post again...

Ah, so your mind is too fragile to handle reading a different forum.
Back to top
View user's profile Send private message
pshongal

New User


Joined: 14 Jun 2012
Posts: 70
Location: India

PostPosted: Wed Jun 27, 2012 5:04 pm    Post subject:
Reply with quote

Hi Naish,
I changed it as below...still no luck..

/*REXX*/
CLEAR
START:
CNT = 0
USER = SYSVAR('SYSUID')
"ISPEXEC DISPLAY PANEL(DELEMSCR)"
Message = ' '
If RC ¬= 0 Then
Do
exit
End
If RG = 'E1' Then
SSID = 'DB2D'
Else
SSID = 'DB2T'
ADDRESS TSO "SUBCOM DSNREXX"
IF RC THEN
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT "SSID
QUERYSQL = "DELETE FROM table_name"||,
"ac_num = '"||AC1||"'"||" AND abc = NULL"
rexxvar="COMMIT"

SAY 'DELETING 'AC1
SAY QUERYSQL
ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :QUERYSQL"
ADDRESS DSNREXX "EXECSQL EXECUTE S1"
IF SQLCODE = 0 THEN DO
ADDRESS DSNREXX "EXECSQL rexxvar"
SAY 'ACCT DELETED SUCCESSFULLY'
ADDRESS DSNREXX "DISCONNECT"
END
EXIT
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: Wed Jun 27, 2012 5:14 pm    Post subject:
Reply with quote

I looked up "no luck" in the messages and codes manual and couldn't get a hit. icon_biggrin.gif

You need to display the SQLCODE to start to figure out what happened.

Did you consider using "EXECUTE IMMEDIATE" for your DELETE statement?
Back to top
View user's profile Send private message
pshongal

New User


Joined: 14 Jun 2012
Posts: 70
Location: India

PostPosted: Wed Jun 27, 2012 5:36 pm    Post subject:
Reply with quote

I tried EXECUTE IMMEDIATE, still not working. I displayed RC, it is -1. Does it mean my query is wrong? Below are my program displays

DELETING 3777478550900
DELETE FROM table_name WHERE ac_num = '3777478550900'
RC -1
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: Wed Jun 27, 2012 5:59 pm    Post subject:
Reply with quote

-1 is the RC. You need to look at the SQLCODE. After every EXECSQL statement.

The query looks okay, but from your code it is not clear if you are even connected to DB2. How do you know that the CONNECT worked?

Until you provide this information it is pointless to speculate.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Wed Jun 27, 2012 5:59 pm    Post subject:
Reply with quote

What does your trace show? and please use the code tags to make the program readable. I am not going to struggle through it in the unformatted mess that it is currently displayed - I do not have time.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


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

PostPosted: Wed Jun 27, 2012 6:06 pm    Post subject:
Reply with quote

pshongal wrote:
I tried EXECUTE IMMEDIATE, still not working. I displayed RC, it is -1. Does it mean my query is wrong? Below are my program displays

DELETING 3777478550900
DELETE FROM table_name WHERE ac_num = '3777478550900'
RC -1

Yes; RC = -1 from DSNREXX invariably means the SQL passed to it is incorrect.

The apparent use of table_name is due to sloppy editing on your part, yes?
Back to top
View user's profile Send private message
pshongal

New User


Joined: 14 Jun 2012
Posts: 70
Location: India

PostPosted: Wed Jun 27, 2012 6:19 pm    Post subject:
Reply with quote

@Akatsukami
Yes..right.

I will use trace and try to find where its going wrong.
Back to top
View user's profile Send private message
pshongal

New User


Joined: 14 Jun 2012
Posts: 70
Location: India

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

I tried with TRACE,
When it comes to execute statement ADDRESS DSNREXX "EXECSQL EXECUTE S1" RC is -1 and sqlcode is -104 and sqlstate: 42601.
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Thu Jun 28, 2012 1:07 pm    Post subject:
Reply with quote

Please check the explanation and programmer response for the SQLCODE -104
Code:
QUERYSQL = "DELETE FROM table_name"||,
"ac_num = '"||AC1||"'"||" AND abc = NULL"

Here, " AND abc = NULL" shouldn't be "AND abc IS NULL"?
Back to top
View user's profile Send private message
pshongal

New User


Joined: 14 Jun 2012
Posts: 70
Location: India

PostPosted: Thu Jun 28, 2012 1:13 pm    Post subject:
Reply with quote

Hi Gnana Sekaran.

I infact removed that condition itself.

Code:
QUERYSQL = "DELETE FROM table_name WHERE"||,
"ac_num = '"||AC1||"';"

Still same result.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 28, 2012 1:16 pm    Post subject:
Reply with quote

what is the datatype of column ac_num?
Back to top
View user's profile Send private message
pshongal

New User


Joined: 14 Jun 2012
Posts: 70
Location: India

PostPosted: Thu Jun 28, 2012 1:18 pm    Post subject:
Reply with quote

Hi dbzTHEdinosauer, its CHAR
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 28, 2012 1:44 pm    Post subject:
Reply with quote

char(?) and are the numbers left or right justified and are they padded with spaces, zeros or what?
Back to top
View user's profile Send private message
pshongal

New User


Joined: 14 Jun 2012
Posts: 70
Location: India

PostPosted: Thu Jun 28, 2012 2:40 pm    Post subject:
Reply with quote

Hi dbzTHEdinosauer,

CHAR(28), Its left justified and padded with spaces.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 28, 2012 3:24 pm    Post subject:
Reply with quote

ILLEGAL SYMBOL "token". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: token-list

that is the message returned with an sqlcode -104.

you need to call dsntiar for that.

here are a couple of links:
calling dsntiar from rexx by don leahy
you may want to look into "RXSQLCA"
here is one from PRINO

without the info, we are just guessing - no we are wasting time.
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm
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. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 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


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