Saturday, October 2, 2010

DB2 DBA(Z/os) Interview questions - Part 2(utilities)

Q1)What are all the Reorg varieties ?
Ans: Online reorg and offline reorg.
Offline reorg: To run an offline reorg we need to mention SHRLEVEL NONE in the utility cntl card. In offline reorg other users/processes will not be having any access to the tablespace.
Online reorg: SHRLEVEL REFERENCE or SHRLEVEL CHANGE will give other users/processes read access or read-write access respectively.

Q2) What are all the reorg phases explain them?
you need to tell what happens in each phase.

Q3) What is the difference between LOAD RESUME YES and LOAD RESUME NO ?

DB2 DBA(Z/os) interview questions -Part 1 (General)

I have attended quite a number of interview calls , I am listing those questions with answers.....

Q1)What is the DB2 version you are working on?
Ans: To know the Db2 version issue -DIS GROUP command from your SPUFI main panel command option.
Q2)What is the Z/OS version you are working on?
Ans: I don't know a command to know this , but you should be knowing your Z/os version ..the versions are like V1.8 ,v1.9, v1.10
Q3)How do you restart a job from a particular step in a proc?
Ans: add a paramater RESTART=procname.stepname in the job card and submit the job.
Q4)What are all versions you have worked on? Can you list some differences between the versions you have worked on?
Ans: Here you need to tell the versions and few advancements in latest versions.

I will keep on adding here..........

Wednesday, September 8, 2010

Accidentally dropped table/tablepsace recovery procedure using DSN1COPY

The recovery procedure for dropped table/tablespace is quite different from the normal recovery procedure we do in case of a data corruption in a table.

When the table/tablespace is dropped all its corresponding information from catalogue tables is flushed out. So no information can be found in catalogue tables once a drop committed.

All image copies are recorded in SYSIBM.SYSCOPY table. If it is a full image copy the ICTYPE column will have ‘F’, if it is an incremental image copy the column will have a value ‘I’. Once the table/tablespace is dropped it deletes copy records from SYSIBM.SYSCOPY table. Notice that DB2 deletes only copy details from its catalogue table, it doesn’t delete the actual image copy dataset.

As the copy details are removed from SYSIBM.SYSCOPY, though we recreate the table/tablespace we can not restore the data using RECOVER utility. The reason is RECOVER depends on SYSIBM.SYSCOPY table for restoring the data. As there is no image copy entry in the SYSCOPY table it doesn’t do a recovery though image copy dataset really exists.

In such a scenario we need to go for a recovery using DSN1COPY.
DSN1COPY is a stand alone utility to copy VSAM datasets. We can utilize this capability of DSN1COPY to restore the dropped table/tablespace bypassing DB2.

To do the recovery a latest full image copy dataset should be available that is taken with SHRLEVEL REFERENCE. Then follow the below procedure.

1) Create the tablespace/table that is dropped. When you create an object in DB2 it is assigned new unique identifier across DB2 subsystem. Record the PSID of tablespace newly created and record the OBID of a table newly created.
To get these you can use below queries.

WHERE NAME=’tablespace_name’;

WHERE NAME=’table_name’ AND CREATOR=’owner_name’;

If the tablespace/table got dropped has LOB column then the auxiliary table will also get dropped automatically. In this case an image copy of LOB tablespace should also be available. This image copy should have taken at the same point along with base tablespace, if not the recovery will not be successful.

2) Stop the tablespace.
            To stop the tablespace issue below command
            -STOP DB(dbname) SPACENAM(tsname)

If the tablespace/table you are recovering has LOBs in it, then the auxiliary tablespace also must be stopped.

3) Run DSN1PRNT job to print the image copy dataset. DSN1PRNT utility reads image copy and prints the pages in hexadecimal format.

DSN1PRNT outputs different pages in the tablespace.
Header page
Space map page
Dictionary page
Data page

Header page contains DBID, PSID values in hexadecimal format.
Data pages contain OBID values in hexadecimal format.

Find the field HPGOBID in the header page section of the DSN1PRNT output. This field contains the DBID and PSID for the table space.

Field HPGOBID is 4 bytes long and contains the DBID in the first 2 bytes and the PSID in the last 2 bytes.

Find the PGSOBD fields in the data page sections of the DSN1PRNT output. These fields contain the OBIDs for the tables in the table space. If you have multiple tables in the tablespace you need to search for all different values of PGSOBD fields to get all OBIDs.

If you print a LOB tablespace using DSN1PRNT look out for HPGROID field in the header page. This is the OBID of the large object (LOB).

Record all new PSID , OBIDs.

4) Run a DSN1COPY job to copy the image copy data into tablespace vsam dataset directly.
When you are copying the data you need to reset the old PSID of tablespace to new PSID and old OBIDs of tables to new OBIDs

Run DSN1COPY job with PARM=’FULLCOPY,OBIDXLAT,RESET’ option.  Providing OBID conversion information in SYSXLAT DD card as shown below

  400,400                                 (old DBID, new DBID)
  14,15                                     (old PSID, new PSID)
  15,16                                     (old OBID, new OBID)

If you have a LOB also, run the DSN1COPY job again with LOB Image copy.

Both jobs should complete with return code 0.

5) Start the tablespace for read/write access.
Auxiliary tablespace also need to be started for LOBs.

To start the tablespace issue below command
            -START DB(dbname) SPACENAM(tsname)  ACCESS(RW)

6)Create & build all indexes on the base table and LOB table.

7) Run CHECK DATA on base tablespace if the table is participating in any referential relationship or if it contains LOB columns. This job should complete with return code 0, other than 0 and if it returns any violations correct them.

8) Run a SELECT query on the table, it will return the data. It means table is recovered.

Tuesday, September 7, 2010

Group discussion do's and don'ts

I have monitored some group discussions conducted for freshers. GD conductors keenly look at below things to select the candidates. 

1. Express your opinion on the topic. either you oppose it or support it.

2. Stick to the core GD topic.. don't deviate

3. Listen carefully to others. Though you don't have an initial idea on the topic, by carefully listening to others  you can come to an idea , then you can start your debate.

4. Talk with the group not with the GD conductor. look at your group members.

5. Express  your ideas with confidence though you don't have much knowledge on the subject

6. No hurry, talk slowly , express your ideas clearly. don't beat around the bush.

7. Stick to the statement you made until your conclusion.

That's it..........your name will be there is select list.


Monday, September 6, 2010