SQL Query using REXX
Moderators: Veera, Moderator Group
-
- Member
- Posts: 11
- Joined: Thu Aug 04, 2011 8:20 pm
- Location: bangalore
SQL Query using REXX
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.
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 Singh
-
- Member
- Posts: 11
- Joined: Thu Aug 04, 2011 8:20 pm
- Location: bangalore
-
- Member
- Posts: 11
- Joined: Thu Aug 04, 2011 8:20 pm
- Location: bangalore
@ DikdudeDikDude 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.
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
Jasdeep Singh
Unfortunately, no. I do not believe a "simple select" will do what you want.. .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?
You will probably need to invest a bit of work beyond a simple select.
Have a good one
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
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.
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.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
-
- Member
- Posts: 11
- Joined: Thu Aug 04, 2011 8:20 pm
- Location: bangalore
Hello DikDude!!!!!DikDude wrote:Unfortunately, no. I do not believe a "simple select" will do what you want.. .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?
You will probably need to invest a bit of work beyond a simple select.
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
Jasdeep Singh
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 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.
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.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
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 the process is to look at all of the other subsystems when there is a NOT FOUND, what input does a user provide?
You should also clarify the requirement. First you say:
then later 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.
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
-
- Member
- Posts: 11
- Joined: Thu Aug 04, 2011 8:20 pm
- Location: bangalore
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?
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 Singh
-
- Member
- Posts: 11
- Joined: Thu Aug 04, 2011 8:20 pm
- Location: bangalore
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?
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
Jasdeep Singh
-
- Member
- Posts: 11
- Joined: Thu Aug 04, 2011 8:20 pm
- Location: bangalore
DikDude wrote:Have you done this?
http://publibz.boulder.ibm.com/cgi-bin/ ... 0713093635
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 Singh
-
- Member
- Posts: 11
- Joined: Thu Aug 04, 2011 8:20 pm
- Location: bangalore
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
Jasdeep Singh
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
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page