Execute DB2 commands in JCL – Health Check


I used to wonder, what if a DB2 object of which you are a DBA is in a restricted status and its time for the the daily batch flow to be released on the CA7 ? Well you will end up getting a page/event if you are oncall. I seriously wouldn’t want to break my goodnight sleep and wake up for an action that could have been avoided, and I am sure you would not like it to. You would eventually fix it, but hey the damage is done on that job which abended with a return code <> 0

So here is a simple health check JCL that can be installed in your shop, may be simplest of all which would perform the check and would notify you well prior to the startup task/production job flow. I am most certain sure that this is installed in your shops, but the excitement of simplicity of this job makes the difference.

Here is your JCL :

//*Your standard JOB card goes here
//*Change STEPLIB as per your account
//                        DISP=(MOD,CATLG,DELETE),
//                        SPACE=(CYL,(1,1),RLSE)
//SYSTSIN     DD *
//SYSIN          DD DUMMY
//*Mail step goes here


This command would populate all the objects that were in the restricted status such as COPY pending, RBLD pending etc.


This command would populate all the objects that were in a advisory state such as AERO* etc.


How easy was it ? I have included all the databases as an example in the display command, it is your choice if you have a selected set of objects in mind. If you are good with REXX programming then you can optimize your JCL to send you emails only when there is a problem. All you now have to do is schedule this JCL in your environment prior and post the daily/weekly/monthly/quarterly/yearly batch cycle depending on your needs and it will proactively notify you of your DB2 object status.


Leave a comment

Need to go a level up on your DB2 skills and learning ? Start with the DSNZPARM’s. Quickest way to explore the DSNZPARM setting for your subsystem using CA Platinum :

1) Navigate to the RC/Query Main Menu window in the platinum database administration tool


2) DB2 Object is SY ( System ) and option is Z ( ZPARM )


3) The above would list out the DSNZPARM’s as shown below, you need to start somewhere – Why not take them one by one when you find time ?


Webcast : DB2 11 for z/OS! Even more cost savings, faster upgrades and enhanced analytics


A lot of DBA’s on the mainframe world are excited about DB2 11 for z/OS. I don’t think there is any reason why they shouldn’t be ! Everyone wants to gets a hands on DB2 11 at the earliest. DB2 11 for z/OS is primarily focused on analytics with some cool DB2 features ( drop column for example ), better performance and much more. Got some questions to ask on DB2 11 ? Well, its your chance. Don’t miss the DB2 11 launch webcast tomorrow, 22nd October at 11:00 EDT  ! All it needs is a registration and your time, the rest is free of cost !

Register for this webcast by clicking here

All attendees will receive a complimentary executive white paper, ” Business Value of DB2 11 for z/OS – Unmatched efficiency for BIG DATA and analytics ” by Julian Sthuler. Rumors are true, one lucky live attendee will get an IDUG 2014 free pass to IDUG NA in Phoenix or IDUG EMEA in Prague. This came in from The world of DB2, Big Data and Business Analytics

Some Highlights retrieved from IBM System z Website :


  • How to achieve these CPU savings:
    • Up to 10% for complex online transaction processing (OLTP)
    • Up to 10% for update intensive batch workloads
    • Up to 40% for complex reporting


  • Joachim Limburg, Director, DB2 for z/OS, IBM Software Group
  • John Campbell, Distinguished Engineer, DB2 for z/OS, IBM Software Group
  • Jeff Josten, Distinguished Engineer and Chief Architect for DB2 for z/OS, IBM Software Group
  • Terry Purcell, Senior Technical Staff Member, IBM Software Group
  • IBM Client: Conrad Wolf, Database Manager at Golden Living
  • IBM Business Partner: Julian Stuhler, IBM Gold Consultant

How to make your DB2 program work in a DRDA setup ?


DRDA – Distributed Relational Database Architecture.

Couple of you will have this question – Why do you need the DRDA set up ? The answer will reside on the early pages of your account when it was set up. We cannot go back to those days, but let me try to explain the architecture from a DBA’s perspective.

You might have two separate production LPAR’s ( APAR & BPAR ) each with a single subsystem ( APAR – ASUB, BPAR – BSUB ). Business decided that from ASUB I should be able to talk to BSUB. If ASUB has the DB2 objects ( database, tablespace, tables ) then BSUB will have a reference using an ALIAS to the object in ASUB.

How to make your DB2 program work in a DRDA setup ?

All I have to do is link the program to the remote object using the below BIND structure on the DB2 PLAN. The program in the form of a DB2 package has to exist in ASUB and BSUB, but the PLAN need not if you are using the below BIND syntax :


The above syntax is from APAR, ASUB – PROG001 is mapped on to PLAN001 and the syntax of PLAN001 is tweaked so that it is able to talk to BSUB using DRDA protocol

Note : This PLAN should exist or should be created in the region where you want to execute the program which in our case is ASUB. This plan need not exist in BSUB, however it is mandatory that PROG001 should exist in ASUB as well as BSUB.

Is there any other way ? Yes -

1) The below syntax is from APAR, ASUB – PROG001 is mapped on to PLAN001 – PROG001 accesses the ALIAS which in turn access the TABLE using the DRDA protocol on BSUB


2) The below syntax is from BPAR, BSUB – PROG001 is mapped on to PLAN001 – PROG001 accesses the TABLE and waits for the connection in case PROG001 present in ASUB is executed from APAR


Note : The PLAN names in both the LPAR’s are the same, Unlike the previous example in which we only had one PLAN which would establish a connection, In the above example we saw two plans being created that would suffice the DRDA establishment

The BIND note :

Both these programs should have a contoken match else will end up with a -805. In other words, the programmer should not miss any BIND’s in the remote/physical region when he is working with DRDA protocol.

Feel the DB2 10.5 BLU on the DB2 Nightshow

Leave a comment

I have had some cool learning experiences attending tech conferences on DB2 10.5 BLU acceleration and I have thoroughly enjoyed watching how robust and user friendly is the design of DB2 10.5 BLU acceleration is – from installation to performance.

18th October 2013 – It’s your chance to meet the experts Randall Ibbott & Scott Hayes when they take your through their learning, experiences and test results on DB2 10.5 BLU acceleration. As most of you are aware that the tables in this DB2 10.5 BLU database edition are organized by column(s) instead of a row(s). Well, I am novice in the LUW world, but I really don’t want to miss this session and if you are from the LUW world I am sure you have already registered.

Even if we miss it – dbisoftware is very kind enough to provide the replays for free and I can watch it at my convenience. Watching them live has its advantages – You can ask them questions, participate in polls and much much more.

I have already registered, If you want to learn and have fun with DB2 10.5 BLU – register for this free education session by clicking here. See you tomorrow !

AREO* – Advisory Reorg Pending : How do I clear this flag ?

Leave a comment

Have you ever seen this flag set while you displayed your database ?


How does this flag get set on a tablespace ?
If you are on DB2 8 for z/OS – You would have increased the length of a column from CHAR(05) to CHAR(10) for example
If you are on DB2 9 for z/OS or higher – You would have appended a column to a DB2 table or increased the length of a column from CHAR(05) to CHAR(10) for example

Is the data in the table still accessible while the flag is active ?
Yes, you can use the table for your application needs without any problem.

What are the different ways of clearing this flag ?

It says Advisory Reorg pending, so a REORG will clear the flag for sure. You can perform a REORG with SHRLEVEL CHANGE, REFERENCE or NONE to clear this flag.

REPAIR utility with its SYSIN DD control having REPAIR SET DATABASE.TABLSPACE NOAREOPENDSTAR will clear the flag

Unlike other force start methods which would clear a copy pending or check pending flags, Is it possible to clear the AREO* flag using similar force start methods ?
No, it is not possible. Well try to clear the flag using this method. The output would display :

OBJECT WILL REMAIN STARTED FOR RW ACCESS                                       

The above display message states that database PRDNTS01 is started for RW access. Perform the display again on the same – you will see that AREO* status is still set on the tablespace, it won’t vanish that easily.

As a DBA, REORG should be the optimal solution for clearing the AREO* status, remember you get a free REBUILD of the index if you perform a REORG as compared to a REPAIR.

BIND Plan or BIND Package – A million $ question


I have been asked by most of my developers – ” Harsha, should I bind the PLAN after I BIND the package ” ? . Well, that depends and DBA’s are the most appropriate point of contact to answer such questions.

This post is streamlined for developers and DBA’s having 0 % existence of PLAN based DBRM in their shops. Yes, the shop will function with only a PLAN – PACKAGE combination. Why ? – Well, get used to the fact that version 10 is already out there – you have to move on with technology.

I will try to explain this complexity of whether to BIND the PACKAGE or the PLAN in two different scenarios -

Scenario 1 :

I have a new release and I plan to implement a new program PROG001. I plan to map this program under an existing collection ID – COL1.PROG001 so that PLAN001 which is capable of executing all the programs under COL1 collection ID ( COL1.* ) can be made use of. How do I do it ?

Use the below BIND PACKAGE Syntax to perform the BIND :


Note the PLAN001 Syntax below generated in the BIND format :


Note that since you used COL1.PROG001, Plan PLAN001 need/should not be bound again as it already has COL1.* in the PKLIST, so if you execute your program using PLAN001.

Scenario 2 :

I have a new release and I plan to implement a new program PROG002. I plan to use a different PLAN for executing PROG002. Also I want to create a different collection ID COL2 for this program. How do I do it ?

Use the below BIND PACKAGE Syntax :


Use the below BIND PLAN Syntax to have a new PLAN, PLAN002 entry in the system and to make use of this plan to execute PROG002 :


Note that since you created a new program with a new collection ID, none of the PLAN’s in your subsystem can be made use of until they are modified/created as they are not designed to make use of your newly created collection ID. As per the requirement you should create a plan which will execute your program as shown above.

Summary :
Developers need not hesitate in asking their DBA’s these questions and DBA’s should never hesitate in helping them out, after all we all learn together.

Older Entries


Get every new post delivered to your Inbox.

Join 713 other followers

%d bloggers like this: