Db2 Interview Questions 4y5v46

DB2-IMS - Few Difference Cobol-DB2

1 2 3 4

Need to Prcompile to generate 1. DBRM (Sql Staments only) 2. Cobol Object code Bind the DBRM to Generate Plan. Plan consists of Executable SQL Quries Compile Cobol Code Execute Cobol - DB2 Program using IKJEFT01, where in we Specify Program Name and Plan Name to be Executed

Cobol -IMS

No Precompilation Required No Bind Process is Required. Here we Write PSBs to fetch respective Record. Compile Cobol-Ims program

Execute Cobol-IMS program using DFSRRC00. P Name , PSB, Name, Plan name Thru Parm Parame

Why We need BIND In DB2 not in IMS Say below query is written in Cobol-Db2 Program

Equivalent IMS Call

1 Select A, B, C Where A=10

First Define PCB to Fetch the field A, B, C Using DLI Call fetch the field A, B, C specified in CALL CBLTDLI USING GU , PCB-NAME , SSA (Populate Key value 10 to SSA) 2 Above Query turned to executable query by binding Plan compile PSB (can hold n no of PCBS) use this plan to execute the Program (while executing Using This PSBLIB to execute the CBLTDLI call Cobol- Db2 Program, when a DB2 statement is executing Cobol-IMS Program, when a DLI /IMS 3 encountered, system refers to plan to execute respective encountered, system points to respective segment query) using respective PCB)

Q.What is check pending and copy pending status in Database? A. 1.Check pending status of a table is set on when you are loading into table with log no and that table has some referencial constarint with other table. copy pending status is set on when you are loading into a table without a NOCOPYPEND option. 2. If the table is loaded with ENFORCE NO option then the table space get into check pending status. That means table space is loaded without enforcing constraints. CHECK utility needs to be run on table space.If table is loaded with LOG NO option then it get's into oy pending status. The meaning is image copy is needed on table space. What is a DB2 bind? A.A DB2 bind is a process that builds an access path to DB2 tables .A BIND process establishes a relationship between an APPLICATION PROGRAM and its RELATIONAL DATA.

How is a typical DB2 batch pgm executed ? A. 1.Use DSN utility to run a DB2 batch program from native TSO. An example is shown: DSN SYSTEM(DSP3) RUN PROGRAM(EDD470BD) PLAN(EDD470BD) LIB('ED 01T.OBJ.LOADLIB') END What do you need to do before you do EXPLAIN? A.Make sure that the PLAN_TABLE is created under the AUTHID Q.Where is the output of EXPLAIN stored? – A.In id.PLAN_TABLE Q.What is a DBRM, PLAN ? A. 1.DBRM: DataBase Request Module, has the SQL statements extracted from the host language program by the pre-compiler. 2.PLAN: A result of the BIND process. It has the executable code for the SQL statements in the DBRM. Q.What is ACQUIRE/RELEASE in BIND? A.Determine the point at which DB2 acquires or releases locks against table and tablespaces, including intent locks. What are PACKAGES ? – A.They contain executable code for SQL statements for one DBRM. Q.What are the advantages of using a PACKAGE? A. 1.Avoid having to bind a large number of DBRM into a plan 2.Avoid cost of a large bind 3.Avoid the entire transaction being unavailable during bind and automatic rebind of a plan 4.Minimize fallback complexities if changes result in an error. Q.What is a collection? A.A defined name that is the anchor for packages. It has not physical existence. Main usage is to group packages.In SPUFI suppose you want to select max. of 1000 rows , but the select returns only 200 rows.