IBM Mainframe IMS DB Interview Questions and Answers.
I need to load production size tables into a test region, should I have my indexes dropped before I load the tables, then request the indexes be recreated?
Answer : No. It is usually more efficient to define all indexes before using the LOAD utility. The LOAD utility uses an efficient algorithm to build the DB2 indexes.
In QMF, I only want the top ten rows from my QMF Query. What is the easiest way to do this?
Answer : Use the ROWLIMIT option on your RUN command: RUN queryname (ROWLIMIT=10
In QMF, how can I see what the Global Variables are set to?
Answer : Enter SHOW GLOBALS on the QMF command line.
How can I tell if my IMS database is broken?
Answer : Records in an IMS database are accessed by using pointers. There are forward and backward pointers in the data portion of the database. During update, insert, delete transaction that abends in-flight, pointers may become corrupted. An unload of the database using BMC Unload plus will tell you if you have pointer errors because this follows the forward pointer chain.
To recover from this situation you will need to go back to your last good image copy and recover. CICS or batch processing will have to be rerun or recovered with logs to get the data back to the point before the in-flight transaction abended. An image copy does NOT indicate if a database has invalid pointers.
What is the difference between an HDAM and HIDAM IMS database?
Answer : A HIDAM database uses a separate index file based on the keyfield of the root segment. This file contains the pointer information that points to the data record. An HDAM file has no separate index file, however, the key field for each record is put thru a randomizer which puts the record to a specific location in the database.
What does DBCTL do?
Answer : DBCTL is an address space, which accesses IMS files on behalf of CICS Transactions. IMS files, PSBs, DBDs, and ACBs are genned into DBCTL for file access. When a CICS program issues an IMS call, the IMS processing is transferred to DBCTL to be executed with a result set being sent back.
Explain what is the significance of a secondary index in IMS?
Answer : A secondary index is an alternate path into you IMS database. Fields from an IMS segment are put together to form a key into that segment. A secondary index file may be used as a file itself to get pertinent data.
I have an IMS program that currently reads and prints information from an IMS database. I now need to update the database from this program. What needs to be done apart from the program changes?
Answer : You need to request the DB group to change the segment sensitivity of the PSB. Also, if any different segments on the database need to be processed, we need to know so that the appropriate access may be granted.
Why do we need to code COMMITS in our batch programs?
Answer : You want to have commit statements in your programs because when you COMMIT you release locks held for that unit of work and allow for a new unit of work to Ans: begin. If you didn't code commits and your program abends well into the processing, instead of just trying to roll back a few inserts since the last commit it has to roll back all the inserts made during the entire run of the program. Usually figure about 2 to 2.5 times the amount of time to rollback that it took to get to the point of abend. There are some exceptions to the above rule.
How do I communicate with DB2?
Answer : There are three methods of communicating with DB2. They are static SQL, embedded dynamic SQL, or the Call Level Interface (CLI).
Static SQL: SQL statements, embedded within a program that are prepared during the program preparation process (before that program is executed). After being prepared, the SQL statement does not change (although values of host variables specified by the statement might change).
Dynamic SQL: SQL statements that are prepared and executed within an application program while the program is executed. In dynamic SQL, the SQL source is contained in host language variables rather than being coded into the application program. The SQL statement can change several times during the application program's execution.
CLI: A callable application program interface (API) for database access, which is an alternative to using embedded SQL. In contrast to embedded SQL, DB2 CLI does not require the user to precompile or bind applications, but instead provides a standard set of functions to process SQL statements and related services at run time.
What is a deadlock or timeout in DB2?
Answer : A deadlock occurs when two separate processes compete for resources held by one another. A timeout is caused by the unavailability of a given resource. The sqlcode for a deadlock is -911 or -913.
What is DRDA?
Answer : Distributed Relational Database Architecture (DRDA) is a connection protocol for distributed relational database processing that is used by IBM and vendor relational database products. DRDA comprises protocols for communication between an application and a remote relational database management system (RDBMS), and communication between RDBMSs. DRDA provides the connections for remote and distributed processing.
How do I find SQL performance problems prior to migrating programs to production?
Answer : SQL Performance analysis should start as early as possible in the development cycle. Performance problems can reside within the SQL statements itself as well as the access paths. Prior to moving to production, you should run an Explain on each program and the access paths should be checked for possible performance problems. The PLAN_TABLE identifies where work is being performed within each access path. You can also use Platinum's Plan Analyzer tool to do the Explain and get an interpreted report back instead of looking at the PLAN_TABLE entries and interpreting them manually.
How can I tell if my SQL statement is using an index?
Answer : Do an explain on the statement or program which will give details as to the access path the optimizer has chosen.
Why do we have to run runstats in our test environment?
Answer : If you don't the catalog columns will have default values in them, which may not reflect the real picture. By using the default values the DB2 optimizer will not pick the best access path.
