SQL Query using REXX

Ask/Clarify the questions on TSO, CLIST & REXX

Moderators: Veera, Moderator Group

jasdeep.ja
Member
Posts: 11
Joined: Thu Aug 04, 2011 8:20 pm
Location: bangalore

SQL Query using REXX

Post by jasdeep.ja » Thu Aug 04, 2011 9:50 pm

Hi All,

I joined this forum recently and i have a requirement.Please help. Consider below:

Suppose there are different subsystems for Database e.g: SS1, SS2, SS3

Each subsystem has Information about Employees as below:

SS1 SS2 SS3
-----------------------------------
E1 E4 E7
E2 E5 E8
E3 E6 E9

Now suppose if a user wants to retrieve info about E5.He/She executes the REXX and enters E5. The REXX should-finds out what SS(subsystem) this Employee(E5) belongs to. And displays the these two INFO's (belong to which SS and EMP_NAME)


SELECT EMP_NAME FROM SS1.EMP_TABLE
WHERE EMP_ID=E5;

If it is ROW NOT FOUND (+100) from above query, it should look into the other SS, else stops and displays the INFO.


SELECT EMP_NAME FROM SS2.EMP_TABLE
WHERE EMP_ID=E5;

If it is ROW NOT FOUND (+100) from above query, it should look into the other SS, else stops and displays the INFO.

And similarly for other SS's if required.
T & R
Jasdeep Singh

jasdeep.ja
Member
Posts: 11
Joined: Thu Aug 04, 2011 8:20 pm
Location: bangalore

Post by jasdeep.ja » Fri Aug 05, 2011 9:03 pm

:(
T & R
Jasdeep Singh

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Fri Aug 05, 2011 11:49 pm

It is not clear what you are looking for. You have already posted the answer (if i understand the question).

You need to write the code to run a query to read each subsytem (or a common query that you provide a subsystem parameter) when the original is not found.
Have a good one

jasdeep.ja
Member
Posts: 11
Joined: Thu Aug 04, 2011 8:20 pm
Location: bangalore

Post by jasdeep.ja » Sun Aug 07, 2011 1:48 pm

DikDude wrote:It is not clear what you are looking for. You have already posted the answer (if i understand the question).

You need to write the code to run a query to read each subsytem (or a common query that you provide a subsystem parameter) when the original is not found.
@ Dikdude

Ok..i wil be writing it to read each subsystem
But can u plz help me start over it....i mean

can u help me write a simple select query REXX to read a sub system and display the result of the query on the screen if the SQLCODE is 0?

SELECT EMP_CD FROM SS1.EMP_TABLE
WHERE EMP_NAME='ABC';
T & R
Jasdeep Singh

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Mon Aug 08, 2011 2:31 am

can u help me write a simple select query REXX to read a sub system and display the result of the query on the screen if the SQLCODE is 0?
Unfortunately, no. I do not believe a "simple select" will do what you want.. .

You will probably need to invest a bit of work beyond a simple select.
Have a good one

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Mon Aug 08, 2011 8:36 am

you can start by reading chapter 6 of Squeezing the Most Out of Dynamic SQL with DB2 for z/OS and OS/390

In the event that you don't understand dynamic SQL,
read the complete manual.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

jasdeep.ja
Member
Posts: 11
Joined: Thu Aug 04, 2011 8:20 pm
Location: bangalore

Post by jasdeep.ja » Mon Aug 08, 2011 8:45 pm

DikDude wrote:
can u help me write a simple select query REXX to read a sub system and display the result of the query on the screen if the SQLCODE is 0?
Unfortunately, no. I do not believe a "simple select" will do what you want.. .

You will probably need to invest a bit of work beyond a simple select.
Hello DikDude!!!!!

I understood what you are saying. But what i am saying is

1. First i should start writting a simple Rexx code (Which i have already started on) (for only one Subsystem) to execute a Hard-Coded SELECT query which will display the result if row found.
2. Now, after executing above succesfully, I will replace that HARD-CODED query to replace with a user input.
3. Then i can go on to add more subsytems and finally achieve what i want.

So please help me write the code for Step 1 above. What the code will be?
T & R
Jasdeep Singh

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Mon Aug 08, 2011 9:39 pm

obviously you have not even looked at the link I gave you.

you are possibly under the delusion that we do your work for you.

does not work that way.

you try to do it,
show us your results and explain your problem,
we may help.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

MrSpock
Active Member
Posts: 273
Joined: Wed Jun 27, 2007 5:37 pm

Post by MrSpock » Mon Aug 08, 2011 10:38 pm

I've been waiting for Jasdeep to tell us what the database is. As someone who's very familiar with REXX and has written many REXX SQL queries for Oracle, I had no idea if I might be able to offer anything or not.

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Mon Aug 08, 2011 11:37 pm

Where are you stuck? If you post what you have done so far, someone will probably have a suggestion. . .

You should also clarify the requirement. First you say:
If it is ROW NOT FOUND (+100) from above query, it should look into the other SS, else stops and displays the INFO.
then later you say:
I will replace that HARD-CODED query to replace with a user input.

If the process is to look at all of the other subsystems when there is a NOT FOUND, what input does a user provide?
Have a good one

jasdeep.ja
Member
Posts: 11
Joined: Thu Aug 04, 2011 8:20 pm
Location: bangalore

Post by jasdeep.ja » Thu Aug 11, 2011 9:55 pm

Hi,

Below is my REXX code, that i have written

/*REXX*/
/* ACCESS DB2 TABLES */

/* ASSIGN THE QUERY TO A VARIABLE */
QUERYSQL = 'SELECT EMP_NAME FROM EMP_TABLE WHERE EMP_ID=022002000'

/* CHECK WHETHER THE DSNREXX ENVIRONMENT IS AVAILABLE */
'SUBCOM DSNREXX'

/* IF NOT, THEN MAKE IT AVAILABLE */
IF RC THEN
DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END

/* CONNECT TO THE DB2 SUBSYSTEM */
ADDRESS DSNREXX "CONNECT DBTU" /* EG: DBT2 */

ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"

ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :QUERYSQL"

ADDRESS DSNREXX "EXECSQL OPEN C1"

ADDRESS DSNREXX "EXECSQL FETCH C1 INTO :EMP_NAME"
SAY EMP_NAME

ADDRESS DSNREXX "EXECSQL CLOSE C1"

ADDRESS DSNREXX "EXECSQL COMMIT"

ADDRESS DSNREXX "DISCONNECT"

EXIT

My requirement to write the above code is:
Since i am not taking any user input(values in the query is hard-coded), i want this to display the value of EMP_NAME (for the EMP_ID=022002000)

Result i am getting is:
it displays the string "EMP_NAME" instead of fetching its value from the DBTU table (EMP_TABLE)

I think the problem might be with the line where i am writting
SAY EMP_NAME.
Bt dont know what?

Any suggestions/thoughts?
T & R
Jasdeep Singh

jasdeep.ja
Member
Posts: 11
Joined: Thu Aug 04, 2011 8:20 pm
Location: bangalore

Post by jasdeep.ja » Sun Aug 14, 2011 11:57 am

Hi,


I added a TRACE to my code and below is what i got in trace.



3 *-* /* ACCESS DB2 TABLES */
5 *-* /* ASSIGN THE QUERY TO A VARIABLE */
6 *-* QUERYSQL = 'SELECT ACCT_ID FROM SUTC.ACTBACCT WHERE ACCT_ID=022002000
7 *-* /* DROP RESTABLE. */
8 *-* /* CNT = 1 */
10 *-* /* CHECK WHETHER THE DSNREXX ENVIRONMENT IS AVAILABLE */
11 *-* 'SUBCOM DSNREXX'
>>> "SUBCOM DSNREXX"
+++ RC(1) +++
13 *-* /* IF NOT, THEN MAKE IT AVAILABLE */
14 *-* IF RC
*-* THEN
15 *-* DO
16 *-* S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
17 *-* END
19 *-* /* CONNECT TO THE DB2 SUBSYSTEM */
20 *-* ADDRESS DSNREXX "CONNECT DBTU" /* EG: DBT2 */
>>> "CONNECT DBTU"
22 *-* ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
>>> "EXECSQL DECLARE C1 CURSOR FOR S1"
24 *-* ADDRESS DSNREXX "EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :QUERYSQL"
>>> "EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :QUERYSQL"
+++ RC(-1) +++
26 *-* ADDRESS DSNREXX "EXECSQL OPEN C1"
>>> "EXECSQL OPEN C1"
+++ RC(-1) +++
28 *-* ADDRESS DSNREXX "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
>>> "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
+++ RC(-1) +++
31 *-* ADDRESS DSNREXX "EXECSQL EXECUTE S1 USING DESCRIPTOR :OUTSQLDA"
>>> "EXECSQL EXECUTE S1 USING DESCRIPTOR :OUTSQLDA"
+++ RC(-1) +++
32 *-* SAY 'OUTSQLDA'
OUTSQLDA
34 *-* ADDRESS DSNREXX "EXECSQL CLOSE C1"
>>> "EXECSQL CLOSE C1"
+++ RC(-1) +++
36 *-* ADDRESS DSNREXX "EXECSQL COMMIT"
>>> "EXECSQL COMMIT"
38 *-* ADDRESS DSNREXX "DISCONNECT"
>>> "DISCONNECT"



I see RC's at line number 11, and then line number 24 onwards......what should i replace it with to get a clean RC...any thoughts/ideas?
T & R
Jasdeep Singh

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Sun Aug 14, 2011 12:53 pm

Have a good one

jasdeep.ja
Member
Posts: 11
Joined: Thu Aug 04, 2011 8:20 pm
Location: bangalore

Post by jasdeep.ja » Sun Aug 14, 2011 3:34 pm


2.5.17 Installation step 17: Bind the packages for DB2 REXX Language Support: DSNTIJRX (optional)


Before you can use DB2 REXX Language Support, you must bind DB2 packages that DB2 REXX Language Support uses. Run job DSNTIJRX to do this. Before you run DSNTIJRX, make the following changes:

Add a job statement.
Change DSN SYSTEM(DSN) to DSN SYSTEM(ssid), where ssid is the name of the DB2 subsystem on which you will use DB2 REXX Language Support.
Change all instances of DSN!!0 to your DB2 data set name prefix.
Change all instances of DSNTIA!! to the plan name for the DSNTIAD program.

| Note: This job is not edited by the CLIST and will not be found in the
| SAMPLE LIBRARY from panel DSNTIPT (prefix.NEW.SDSNSAMP). DSNTIJRX
| is included in the target library if you have ordered DB2 REXX
| Language Support.




Now, where i can find this "DSNTIJRX" job.??/help
T & R
Jasdeep Singh

jasdeep.ja
Member
Posts: 11
Joined: Thu Aug 04, 2011 8:20 pm
Location: bangalore

Post by jasdeep.ja » Sun Aug 14, 2011 3:36 pm

jasdeep.ja wrote:Hi,


I added a TRACE to my code and below is what i got in trace.



3 *-* /* ACCESS DB2 TABLES */
5 *-* /* ASSIGN THE QUERY TO A VARIABLE */
6 *-* QUERYSQL = 'SELECT ACCT_ID FROM SUTC.ACTBACCT WHERE ACCT_ID=022002000
7 *-* /* DROP RESTABLE. */
8 *-* /* CNT = 1 */
10 *-* /* CHECK WHETHER THE DSNREXX ENVIRONMENT IS AVAILABLE */
11 *-* 'SUBCOM DSNREXX'
>>> "SUBCOM DSNREXX"
+++ RC(1) +++
13 *-* /* IF NOT, THEN MAKE IT AVAILABLE */
14 *-* IF RC
*-* THEN
15 *-* DO
16 *-* S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
17 *-* END
19 *-* /* CONNECT TO THE DB2 SUBSYSTEM */
20 *-* ADDRESS DSNREXX "CONNECT DBTU" /* EG: DBT2 */
>>> "CONNECT DBTU"
22 *-* ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
>>> "EXECSQL DECLARE C1 CURSOR FOR S1"
24 *-* ADDRESS DSNREXX "EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :QUERYSQL"
>>> "EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :QUERYSQL"
+++ RC(-1) +++
26 *-* ADDRESS DSNREXX "EXECSQL OPEN C1"
>>> "EXECSQL OPEN C1"
+++ RC(-1) +++
28 *-* ADDRESS DSNREXX "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
>>> "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
+++ RC(-1) +++
31 *-* ADDRESS DSNREXX "EXECSQL EXECUTE S1 USING DESCRIPTOR :OUTSQLDA"
>>> "EXECSQL EXECUTE S1 USING DESCRIPTOR :OUTSQLDA"
+++ RC(-1) +++
32 *-* SAY 'OUTSQLDA'
OUTSQLDA
34 *-* ADDRESS DSNREXX "EXECSQL CLOSE C1"
>>> "EXECSQL CLOSE C1"
+++ RC(-1) +++
36 *-* ADDRESS DSNREXX "EXECSQL COMMIT"
>>> "EXECSQL COMMIT"
38 *-* ADDRESS DSNREXX "DISCONNECT"
>>> "DISCONNECT"



I see RC's at line number 11, and then line number 24 onwards......what should i replace it with to get a clean RC...any thoughts/ideas?






Is the RC=1 at line 11 is the problem...then wat culd be the resolution?
T & R
Jasdeep Singh

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others