Solved

DB2 SQL - How can I provide for a variable substitution in my SQL where clause?

Posted on 2012-12-31
6
1,267 Views
Last Modified: 2013-01-09
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=D2,
//           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.LOAD') PARMS('SQL')
  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
0
Comment
Question by:Enyinnaya
  • 4
  • 2
6 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 38733972
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
0
 

Author Comment

by:Enyinnaya
ID: 38737768
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
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 38737795
could you explain what is the point of having those as parameters that are hard coded in the JCL?
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:Enyinnaya
ID: 38740640
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
0
 

Accepted Solution

by:
Enyinnaya earned 0 total points
ID: 38744337
Ok, I think this question is/was wrongly worded hence the lack of responses or answers. I am going to close it...rephrase the question to more accurately reflect what I need assistance with...no points awarded.

Thanks
0
 

Author Closing Comment

by:Enyinnaya
ID: 38758226
No valid answer, no example...rephrasing the question...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Configuration Assistant in DB2 10.1 3 47
DB2 - LOG FILES. 4 30
1 FROM DUAL wont work with additional columns ?? 4 22
Help Extract Specific in SQL 8 19
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now