74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.
With new material every week, we'll make sure that you stay ahead of the game.
COURSE of the MONTH
7 days, 21 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
DB2 SQL - How can I provide for a variable substitution in my SQL where clause?
Posted on 2012-12-31
Hi All,
I need to be able to substitute two variables in this simple SQL code:
The JCL:
//SETVAR SET SSNODE=DBO1,DBOI=P1,DBOO=D
// TRK=OG1,LOADIR=TEST,
// PARMDIR=OGDBA,
// PDBNAME='OG1DD%1', <<-----Database name
// PTSNAME='OG1SC%' <<-----Tablespace Name
//SYSTSIN DD *
DSN SYSTEM(DBO1) RETRY(3)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB81) -
LIB('SYS2.DB2.TEST.RUNLIB.
END
/*
//SYSIN DD *
SELECT '-START DB('||DBNAME||') SPACENAM('||NAME||') ACCESS(UT)' <<<------SQL
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME LIKE %PDBNAME
AND NAME LIKE %PTSNAME
// PDBNAME='OG1DD%1', <<-----Database name
// PTSNAME='OG1SC%' <<-----Tablespace Name
How can make this work? I am getting errors every which way I try!
Your expert help will be highly appreciated
I need to be able to substitute two variables in this simple SQL code:
The JCL:
//SETVAR SET SSNODE=DBO1,DBOI=P1,DBOO=D
// TRK=OG1,LOADIR=TEST,
// PARMDIR=OGDBA,
// PDBNAME='OG1DD%1', <<-----Database name
// PTSNAME='OG1SC%' <<-----Tablespace Name
//SYSTSIN DD *
DSN SYSTEM(DBO1) RETRY(3)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB81) -
LIB('SYS2.DB2.TEST.RUNLIB.
END
/*
//SYSIN DD *
SELECT '-START DB('||DBNAME||') SPACENAM('||NAME||') ACCESS(UT)' <<<------SQL
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME LIKE %PDBNAME
AND NAME LIKE %PTSNAME
// PDBNAME='OG1DD%1', <<-----Database name
// PTSNAME='OG1SC%' <<-----Tablespace Name
How can make this work? I am getting errors every which way I try!
Your expert help will be highly appreciated
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
2
6 Comments
there is no simple way to do that
you can either write a program that gets those parameters and put them in the sql query and then execute the query or generate the input file for dsntiaul, or you can try to use iebgener to modify the input file for dsntiaiul
you can either write a program that gets those parameters and put them in the sql query and then execute the query or generate the input file for dsntiaul, or you can try to use iebgener to modify the input file for dsntiaiul
Ok, moni_sabag, could you kindly describe in more detail with example of what or how you will go about doing what you have in mind?
Thanks
Thanks
First, we are do not have many of the avaiable DBMS management toolset available to us so I am not privileged in that regard.
I have several application tracks and the associated databases. I do not want to have to update many many jobs every time a change is made/added/dropped, etc to tablespaces or tables if I want to QUIESCE, or put Tablespaces in utility mode during DB maintenance.
Also, I want to try to use only one Procedure for all my DB2 environments: 6 QA environment and 4 Test environment not to mention my production environments. Each environment has at least 7 applications/database tracks. My good news is that the database and tablespace are identical in almost everyway. By using symbolic substitution method, I can do this easily I believe.
The problem is, and as you so rightfully stated earlier, there is no easy way (or is there?) to get DNSTIAUL to accept parameter substitution the way I have it in the SQL that you saw before.
All I really trying to achieve here is be able to specify a database and/or tablespaces, select all tablespace that I specify and put them in utility mode or return them to read/write at will without doing any hardcoding!.
This SQL works just fine only if I hardcode a database or a tablespace name. I want to substitute to two parameters dynamically.
I hope this help to clarify what I am trying to achieve.
Thanks
I have several application tracks and the associated databases. I do not want to have to update many many jobs every time a change is made/added/dropped, etc to tablespaces or tables if I want to QUIESCE, or put Tablespaces in utility mode during DB maintenance.
Also, I want to try to use only one Procedure for all my DB2 environments: 6 QA environment and 4 Test environment not to mention my production environments. Each environment has at least 7 applications/database tracks. My good news is that the database and tablespace are identical in almost everyway. By using symbolic substitution method, I can do this easily I believe.
The problem is, and as you so rightfully stated earlier, there is no easy way (or is there?) to get DNSTIAUL to accept parameter substitution the way I have it in the SQL that you saw before.
All I really trying to achieve here is be able to specify a database and/or tablespaces, select all tablespace that I specify and put them in utility mode or return them to read/write at will without doing any hardcoding!.
This SQL works just fine only if I hardcode a database or a tablespace name. I want to substitute to two parameters dynamically.
I hope this help to clarify what I am trying to achieve.
Thanks
Featured Post
Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Recursive SQL in UDB/LUW (it really isn't that hard to do)
Recursive SQL is most often used to convert columns to rows or rows to columns. A previous article described the process of converting rows to columns. This article will build off of th…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
- Query Syntax
- SQL
- Databases
- MySQL Server
- MongoDB
- NoSQL Databases, Percona
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash"
After Shake : http://www.videocopilot.net/presets/after_shake/
All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month7 days, 21 hours left to enroll
765 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.