Connect MS Access to DB2 Express C

Working with DB2 Express C for a while I tried to explore a way through which I can connect any front end application and see if I can get a breakthrough with the native DML's I was performing on the DB2 CLP administrator window on my DB2 tables.

Easiest of all connections to DB2 is through Microsoft Access.

Here are the steps through which you can connect to the database :

Step 1 : Launch the MS Access and create a sample database

Step 2 : On the External Data tab choose ODBC database with second bullet option which gives you a two way interface to your DB2 database / Any database for that matter

Step 3 : Select Machine Data Source, Click New

Step 4 : Create a new data source tab, Choose System Data Source and Click Next

Step 5 : Choose the IBM DB2 ODBC Driver - DB2COPY1, Click Next & Finish

Step 6 : Provide a datasource name of your choice and click OK

Step 7 : Double Click on the datasource name that was just created using the above driver

Step 8 : The connection to DB2 will now be established and you will be able to see all the tables under the database you created the DSN on

Step 9 : Use MS-Access as a front end appliance for your DB2 database

Easy to install, Easy to use. The data you feed into MS-Access directly replicates on the DB2 data server, the only draw back is - the refresh wouldn't work and you will not see data changes when someone updates your database untill and unless you close and re-establish the connection, I am still trying to figure out a way if its possible.

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
//HEALTH  EXEC PGM=IKJEFT01
//*Change STEPLIB as per your account
//STEPLIB    DD DISP=SHR,DSN=SHOP.PRDN.DSNEXIT
//                 DD DISP=SHR,DSN=SHOP.PRDN.DSNLOAD
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD DSN=HLQ.HEALTH.CHECK,
//                        DISP=(MOD,CATLG,DELETE),
//                        SPACE=(CYL,(1,1),RLSE)
//SYSTSIN     DD *
DSN SYSTEM(PRDN)
-DISPLAY DATABASE(*) SPACE(*) RESTRICT
-DISPLAY DATABASE(*) SPACE(*) ADV
//SYSIN          DD DUMMY
//*Mail step goes here

-DISPLAY DATABASE(*) SPACE(*) RESTRICT

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

-DISPLAY DATABASE(*) SPACE(*) ADV

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

Summary:

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.

CMD : SY Z

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 andenhanced 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 :

Topics

• 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

Speakers

• 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

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 ?

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 ?

REORG:
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:
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 :

DSNI015I  -PRDN OBJECT PRDNTS01 IS ALREADY STARTED FOR RW ACCESS. THE          
OBJECT WILL REMAIN STARTED FOR RW ACCESS                                       
DSN9022I  -PRDN DSNTDDIS 'START DATABASE' NORMAL COMPLETION         

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.

Summary:
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.