REXX- delete query in REXX

pshongal · New User Joined: 14 Jun 2012 Posts: 96 Location: India

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

Naish · New User Joined: 07 Dec 2006 Posts: 82 Location: UK

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

Also, why did you post again???

pshongal · New User Joined: 14 Jun 2012 Posts: 96 Location: India

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

Since moderator had put my query in DB2 forum, I had to post again...
[/quote]

Naish · New User Joined: 07 Dec 2006 Posts: 82 Location: UK

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?

Akatsukami · Posted: Wed Jun 27, 2012 4:55 pm

pshongal · New User Joined: 14 Jun 2012 Posts: 96 Location: India

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

don.leahy · Posted: Wed Jun 27, 2012 5:14 pm

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

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

Did you consider using "EXECUTE IMMEDIATE" for your DELETE statement?

pshongal · New User Joined: 14 Jun 2012 Posts: 96 Location: India

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

don.leahy · Posted: Wed Jun 27, 2012 5:59 pm

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

Nic Clouston · Posted: Wed Jun 27, 2012 5:59 pm

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.

Akatsukami · Posted: Wed Jun 27, 2012 6:06 pm

pshongal · New User Joined: 14 Jun 2012 Posts: 96 Location: India

@Akatsukami
Yes..right.

I will use trace and try to find where its going wrong.

pshongal · New User Joined: 14 Jun 2012 Posts: 96 Location: India

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.

Gnanas N · Posted: Thu Jun 28, 2012 1:07 pm

Please check the explanation and programmer response for the SQLCODE -104

pshongal · New User Joined: 14 Jun 2012 Posts: 96 Location: India

Hi Gnana Sekaran.

I infact removed that condition itself.

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

Still same result.

dbzTHEdinosauer · Posted: Thu Jun 28, 2012 1:16 pm

what is the datatype of column ac_num?

pshongal · New User Joined: 14 Jun 2012 Posts: 96 Location: India

Hi dbzTHEdinosauer, its CHAR

dbzTHEdinosauer · Posted: Thu Jun 28, 2012 1:44 pm

char(?) and are the numbers left or right justified and are they padded with spaces, zeros or what?

pshongal · New User Joined: 14 Jun 2012 Posts: 96 Location: India

Hi dbzTHEdinosauer,

CHAR(28), Its left justified and padded with spaces.

dbzTHEdinosauer · Posted: Thu Jun 28, 2012 3:24 pm

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.

Akatsukami · Posted: Thu Jun 28, 2012 3:27 pm

pshongal · New User Joined: 14 Jun 2012 Posts: 96 Location: India

Hi Akatsukami,
Thats right.

Stefan · Posted: Thu Jun 28, 2012 4:12 pm

don.leahy · Posted: Thu Jun 28, 2012 5:16 pm

Long thread for such a simple problem.

Marso · Posted: Thu Jun 28, 2012 5:34 pm

About your problem:

In addition to displaying the SQLCODE, you should as well display the SQLERRMC value.
It will tell you which token is erroneous.

About your code:

The SUBCOM to CONNECT commands should be issued before displaying the panel.
If there is a problem connecting to DB2, why let the user loose time in filling the panel.
That will not work:

Quote:

rexxvar="COMMIT"
ADDRESS DSNREXX "EXECSQL rexxvar"
This is nice if you are paid by the line:

Quote:

If RC ¬= 0 Then
Do
exit
End

If not, you could just write "If RC ¬= 0 Then Exit"

About your post:

Use COPY/PASTE or double-check yourself!
"WHERE" is missing from these lines:

Quote:

QUERYSQL = "DELETE FROM table_name"||,
"ac_num = '"||AC1||"'"||" AND abc = NULL"
Learn to use BBCODE !