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

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

DB2Mainframe OSSQL

Last Comment

Enyinnaya

8/22/2022 - Mon

momi_sabag

12/31/2012

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

Enyinnaya

1/2/2013

ASKER

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

momi_sabag

1/2/2013

could you explain what is the point of having those as parameters that are hard coded in the JCL?

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Enyinnaya

1/3/2013

ASKER

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

ASKER CERTIFIED SOLUTION

Enyinnaya

1/4/2013

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question

Enyinnaya

1/9/2013

ASKER

No valid answer, no example...rephrasing the question...