http://www.bsp-gmbh.com/turnkey/

GeekInterview.comFor accessing a VSAM cluster sequentially, STARTBR, READNEXT, ENDBR commands are used. Suppose the VSAM cluster is sorted on the basis of a field (lets say age) and using STARTBR pointer is positioned on the record with name 'brijesh'. By using READNEXT that record is read. Now if i again use READNEXT, next record will be read(which could be the record with name other than 'brijesh'). If I want to read the next record with the same name (i.e. brijesh), whether RESETBR will be correct option for me. By using RESETBR here, whether the pointer will be placed at the next record with same name(brijesh) or the first record with the name 'brijesh'. Please explain the same.

GeekInterview.comwhat do you mean by RDW in VSAM?

Guys As you are aware RDW stands for Record Descriptor Award.This is the first 4 bytes in a VSAM - Variable Record Length file that stores the actual length of the record. This is hidden if you browse a VSAM file. In a VB file realistically the record startsfrom COL 5 and NOT COL 1 as you see when you browse the file.

GeekInterview.comWhat do you mean by dirty read

The dirty read technique can be used to provide a high degree of concurrent access to VSAM files while avoiding the complications associated with CI and CA splits. The dirty read protocol can be summarized as follows:

1. The VSAM file must be defined with cross-region SHAREOPTIONS 4. 2. The file must be allocated with DISP SHR. 3. All operations on the file including reads must be preceded with an ENQ for the

data set. The data set name is specified for the ENQ rname but any string can be used for the ENQ qname (though it must be the same string for all accessors).

4. After a lock is obtained the user's buffer must be refreshed with a GET request. This step also applies to new record insertions.

5. At the end of all file operations the enqueue is released with a DEQ function call. 6. No lock is held during a wait (such as terminal input wait). 7. Before updating a previously read record a fresh copy of the record must be

obtained and compared to the original to ensure that no other user has updated the record while it was being browsed and modified. As always the second read the record comparison and the update must be shielded by a lock.

GeekInterview.comIn vsam why we use export-import utility?

EXPORT-IMPORT UTILITY is used to take a back along with the attributes of the VSAM file and restore the back with the same attributes respectively.We can use REPRO to take back up it will not copy the attributes of the file it will only copy data logically. Hance after deliting the original VSAM file we will have to redefine it for restoring.NOTE: IMPORT can be used for restoration only if data has been backed up using EXPORT

GeekInterview.com

What is the maximum record length for the VSAM dataset?Of all the answers provided. The answer provided by DRSCBALAJI is correct. It is Max of 1 cylinder.

The reason he gave is also correct, since there can be SPANNED RECORDS which can cover multiple CI's. But a spanned record can never cross a CA. And the maximum value for a CA is 1 Cylinder. So ultimately the maximum size of a VSAM record will be 1 Cylinder.

GeekInterview.com

What is the maximum no. of records that can be allowed in VSAM?

The Maximum Number of records which can be stored on KSDS depends on various factors,1. The record size and its type(fixed length or variable length). 2. The type and sige of the CA which is defined so to say , either cylinders or tracks etc along with  the primary and secondary quantity.3. The maximum number of extents which we can  get in VSAM are 123 and The percentage free space for CI and CA, being specified during the definition will also plays important role thus I hope considering all these factors, one can come to a conclusion about the approximate records those can be stored on VSAM file.

VSAM now can have 255 EXTENDS

While designing vsam files,what's the best way to choose control interval size for the data and the index?

well it depends on whether the data will be accessed directly or sequentially.

for direct access use small ci for sequential access use larger ci.

RE: while designing vsam files,what's the best way to...

Don't get bogged down unless the file is tremendously large. Rule of thumb: use 4096 for the data and 1024 for the index. You'll never never be far from optimum with those CI sizes. 

Please let me know why we choose data as 4096 and index 1024 in

are they multiples if not then why not 4000 or 1000

explain in details Please?

RE: While designing vsam files,what's the best way to choose control interval size for the data and the index?

Control interval size can be optimally selected by the system when we omit it. Because the C.I. is nothing but the place where a record get stored depending on various factors (such as either a fixed length or variable length record) we have to calculate the C.I size. Don't worry about these issues thanks to some modern developments by the IBM which makes the system to effectively calculate the optimum C.I. size. 

RE: While designing vsam files,what's the best way to choose control interval size for the data and the index?

1024 and 4096 are powers of 2. You could specify 1000 and 4000 but VSAM would actually use 1024 and 4096 although I'm not sure if the full 1024 would actually be available to you. Short answer is that since that is the number VSAM will use 1000 or 4000 will not save you anything.

How do you convert flat files to VSAM files?

Following steps will convert a sequential file to VSAM file.1. Sort the sequential fille, with SORT FIELD=NONE, to remove the duplicate key records.2. Define a VSAM file3. Use REPRO to copy the sequential file to VSAM file.

Let's be a little more precise.We will take the tack that you will be creating a KSDS file.First you need to know which field/s you will be using as the primary key and if you need alternate keys. Sort the records by that field/s.Use IDCAMS REPRO to load the flat file into the VSAM file.

How do you convert flat files to VSAM files

Hi all

You can't convert the flat files to Vsam files. The data it means the record in the flat file will be copied to vsam file where you can have any field as the primary key but it should be unique.

How do you convert flat files to VSAM files?

You can convert flat files to VSAM:1. Identify what will be your primary key. If you don't want to the whole record can be your primary key.2. Sort the file by primary key or the whole record (if no primary key selected) and use the sort statement that drop duplicates.3. Submit a JCL (IDCAMS - Define Cluster) using the output of step #2 as your input.

How do you convert flat files to VSAM files?

Following steps will convert a sequential file to VSAM file.1. Sort the sequential fille with SORT FIELD NONE to remove the duplicate key records.2. Define a VSAM file3. Use REPRO to copy the sequential file to VSAM file.

How vsam datasets are cataloged?

How vsam datasets are cataloged?

When you allocate the vsam cluster at that time you have the option to catlog the dataset using the idcam utility. Apart from this tools like file aid also gives you an option to catalog a vsam dataset.

How vsam datasets are cataloged?

Are you sure? I didn't think you could create a VSAM data set without catalog entries because on the DASD VTOC when it is listed as VS there are limited DSCB entries. I think that if you are creating a catalog entry for a VSAM data set you must be importing it. To create a VSAM data set the only way I am aware of is through IDCAMS which would be associated with some catalog. (Won't swear to all of the above but would be willing to wager a drink on it...)

Why VSAM concept came into existence ?

It is efficient high performance tool allows backup and recovery provides data security and integrity.

Why did VSAM come into existance?

Think DOS VS! Using VSAM was much much easier than coding masses of inflexible DLBL and EXTENT cards for running jobs in DOS VS.

Why VSAM concept came into existence ?

VSAM was written as a replacement for ISAM. The main problem with ISAM was that if there were many insertions and deletions the way he chained the entries was horribly inefficient. VSAM through the use of splitting CIs was a hugh improvement.

What is the vsam mode in which spanned records mus...

we can access the spanned records in the MOVE Mode

http://www.exforsys.com/tutorials/mainframe/vsam-introduction.html

VSAM CatalogsVSAM StructureVSAM Introduction

Mainframe system supports sophisticated operating systems like UNIX, Linux and VMS and IBM operating systems like Z/OS, Z/VM, and VSE/ESA.

What is a Control Interval in VSAM?

Control Interval in VSAM is a unit of data that is transferred when an I/O request is made between auxiliary storage and virtual storage. It contains records, free space and control information. The data records are grouped into control intervals. That is in other words in non-VSAM method the unit of data that is transferred between memory and the storage device is defined by the block which in contrast in VSAM is referred as Control Interval.

The records in the Control Interval are all placed in the beginning of the Control Interval with key of each record followed by the actual record. The end portion of the Control Interval is occupied by control interval descriptor this also has space for specifying the record length of each record in the Control Interval. The space between this beginning part which is occupied by records and the end part of control interval which has descriptor followed by length of record denotes the free space. This is shown diagrammatically below:

Key 1 Record 1

Key 2 Record 2

Key 3 Record 3

Key 4 Record 4

Key 5 Record 5

 

Free Space

 

Length of Record 1

Length of Record 2

Length of Record 3

Length of Record 4

Length of Record 5

 

Descriptor(End part of control interval)

The Control information also called as CI in short consists of the following information:

RDF called as Record Descriptor Field CIDF called as Control Interval Descriptor Field

The maximum size that is allowed for a Control Interval is 32K. Let us now see about the information stored on the above RDF and CIDF.

A CIDF is present for each Control Interval. The CIDF is a field which has the last 4 bytes of a Control Interval. This has information about the offset and the length of free space in the Control Interval. Having seen about the information in CIDF let us now see the information present in RDF. If the records are of fixed size then each Control Interval has two RDF's with each RDF of length 3 bytes. If the records are of variable size then a separate RDF is available for each record in the Control Interval. The Control Interval in VSAM is created as soon as VSAM dataset is loaded after which records are placed inside it. Having got an idea about Control Interval in VSAM from above explanation and idea about KSDS clusters, RRDS clusters and ESDS clusters from our previous section let us now see how the records are filled in the control interval by each of these different clusters.

In case of Entry Sequenced Data Set clusters also called as ESDS clusters which follows sequential data set, records are fully placed in a control interval and only after completing this control interval say the current control interval, the records are written into the next control interval in sequence.

Sponsored Links

In case of Key Sequenced Data Set clusters also called as KSDS which has room for free space as we have seen in our previous section the records are placed in a control interval but not with a condition that it is fully filled which means that some amount of free space is provided for future additions. In case of Relative Record Data Set clusters the control interval is filled with records of fixed length which can be two types say either a active record or a dummy record. Active record refers the original record and the dummy record helps in providing space for future record addition to the dataset as and when needed.

VSAM Control Area

What is VSAM Control Area?

A group of control intervals makes up a control area. Since the control intervals are grouped to form control area the way of filling the control area is similar to the way of filling records in control interval as discussed in detail in our previous paragraph. The number of control intervals per control area depends on how much space is reserved when the dataset is created. This is shown diagrammatically below:

Control Area 1

Control interval A

Control interval B

Control interval C

Control interval D

Control interval E

Control Area 2

Control interval F

Control interval G

Control interval H

Control interval I

Control interval J

Attributes in the index component

The index component has the following attributes as given below:

index set sequence set

The index set lists the largest index within each control area and the sequence set lists the largest index within each control interval.

The records get allocated in Control Interval and Control area in the following manner:

• Check for enough free space within a control interval is made and if such a space is present then the new records gets added in that position.

• If the Check for enough free space within a control interval returns no enough free space within a control interval then a check for the presence of empty control interval within the same control area is made and if such a empty control interval within the same control area occurs the control interval split is made with the record from the control interval which is filled with records being moved to the empty control interval space.

• If in case of Check for enough free space within a control interval returns no enough free space within a control interval and also there is no empty control interval within the

same control area then control area split is made and records gets moved to the new control area.

what is cursor stability? Answer# 1 It is a default isolation level. It puts a lock on the data that has been fetched, so that the retrieved data can be updated or deleted.

 

Db2 All Interview.com

how to copy the host variables from ps file into cobol program other than include statement

by using copy statement we can copy the host variables from the ps file..another method is we will directly declare host structure in the cobol program....

how to check the table is empty or not? Answer# 1 EXEC-SQL SELECT 1 INTO :X FROM TAB1 END-EXEC X is defined as s9(4) comp value zeroes So, if there is a row in the table (i.e. the table is not empty), 1 gets moved to variable X. Did not have resources to run the query and validate ,

what is a trigger and types of triggers Answer# 1 trigger is a set of actions that will be executed or implemented , when a defined event occur. triggering events are 1.insert, 2.update, 3.deletetriggers can be defined for a specific table and once defined then it is automatically active.triggers are used to do a data validation and to maintain data integrity.types of triggers:before trigger and an after trigger.triggers information can be stored in the sysibm.systriggers

 

Re: what is the use of bind parameter, Replace? Answer# 1 A "BIND REPLACE" will require a DBRM as input and will replace the existing data with that supplied in the DBRM. It will then analyse the access paths for the SQL supplied in the DBRM. You tend to run a "BIND REPLACE" when the associated application has been updated so that DB2 is also updated with any changes to the SQL.

 database DSNDB01 means a.db2 catalog b.db2 directory c.BSDS d.active logs answer with reason please Answer# 1 B, DB2 directory. DSNDB01 is a DB2 system database that contains DB2's directory. The directory contains table spaces that hold DBDs(DBD01), Plans(SCT02), Packages(SPT01), information about utilities(SYSUTILX) and system log range records (SYSLGRNX). This data cannot be queried through SQL, only through DB2 commands.

in GDG: is it poosible to copy records from a file which utilizes some 100 cylinders to a gdg whose model is defined as trk(1,0) and also it possible to copy 100 bytes file to 80 bytes file? Answer# 1 It is possible but we losse data.

IBM

DB2: A calls B once sucessfull and second time when it calls says curson is open why is it so?

IBM

WHAT IS NULL INDICATOR ? WERE IS THIS USED? AND IF IT ISUSED IN FILES CONCEPT? HOW DO WE USE IT?

IBM

Re: What is usage pointer in cobol? Answer# 1 Declare a pointer as an elementary data item bearing the USAGE IS POINTER clause, with no PICTURE. E.g:

05 EXAMPLE-P USAGE IS POINTER.

EXAMPLE-P is a four-byte field which can store the address of any data item. Unless you're interfacing COBOL to Assembler or something, you really don't care what the pointer looks like internally. It's just a piece of magic which enables you to access the thing it's pointing to.

IBM

How to solve S0C7 abend with out using DISPlay in 10 mins?

IBM

Parm value passed is PARM=(10,20). how do code linkage section and how would you add these two passes values and show result in cobol program.

UST

Can we use select query in a loop to fetch multiple rows in a COBOL PROGRAM? If so, what is the advantage of cursor?

UST

have cobol+ db2 program but it contains n number of subprograms for this how many dbrm members i need to mention in bind process

ACCENTUREwhat is the differences between spufi and qmf and which is better? Answer# 1 QMF - by using QMF we can execute 1 query at a time,SPUFI - by using SPUFI we can exeute multiple querys at a timeQMF - By using QMF we can we can identify error code SPUFI - BY using SPUFI we can identify error in case of query failure, application programmer use SPUFI.I THINK SPUFI IS BETTER

IBM

Suppose we have a query for update update table1 set col1 = 'val1' where col2=(select .... from ...) suppose the subquery does not return any record, what will happen to update? Answer# 1 If the col2 field having any null value then that value tobe updated.If col2 doesn't have any null value then record remains samenothing will be updated.

SYNTEL

I HAVE 500 ROW TO UPDATE I WOULD LIKE TO USE ROLLBACK ALONG WITH COMMIT.WHAT IS THE SYNTAX TO CODE COMMIT AND ROLLBACK FOR EVERY 100 ROWS.AND HOW THE CURSOR ROLLBACK TO THE LAST COMMITTING POINT.

CAPGEMINI

Re: why we go for SPUFI for creating tables or for sql queries? Answer# 1 By using SPUFI we can get the error code when the query fails, and at a time we can execute multiple queries.QMF doesn't support to execute multiple queries at a time.

CAPGEMINI

: how many bytes timestamp token occupies? Answer# 2 thats correct..10 bytes

CTS

In db2, how do you insert multiple rows in 1 query using a.) Cursors, b.) Normal query?? Give syntax for both. Answer# 1 We can insert by using the following command:-

Insert into tablename (col1,col2,col3,....)values(&emp-no, '&emp-name', .....)

SYNTEL

OUTER JOINS USED TO GET MATCHING AND NONMATCHING ROWS FORM 2 OR MORE TABLES BASED ON COLUMNS. SIMPLE JOINS ALSO DO THE SAME THEN WHAT IS THE USE OF OUTER JOINS. Answer# 1 Simple joins gives only matching/common values from two or more tables

If you have mentioned only Outer joins then it is same as simple join gives the matching values from two or more tables but outer joins are of two types--- right outer join and left outer join

for right outer join--- it will give non matching rows also from right table in addition to matching rowsfor left outer join-- it will give non matching rows also from left table in addtion to matching rows

TCS

how can u retrieve the data from null values using applicaion program. Answer# 1 whenever you are retreving the data from null coloumn, first you need to declare the null indicator in the working storage section. and use this variable when you are fecthing the data from null coloumn.

how can u retrieve the data from null values using applicaion program. Answer# 2 The null indicator would be assigned a value of -1 if the fetched column has a null value.

if any of the column names is provided wrong, where do you get the error(during precompilation or during Bind) Answer# 1 Precompilation has got nothing to do with the column checking, it is just the process of syntax checking and separating the sql statements from the cobol program and just prepares the dbrm.

actual process for db2 takes place in bind card

 The above answer is correct but not always.

If we use DECLARE TABLE in the program then the table and columns names are validated in the pre-compilation step itself.

Re: Do we need cursor for Count(*)? Answer# 1 No,Count(*) will give u the number of record in a table.

Ex: select Count(*) from EMP table where EMPNAME='sunil'

we will use cursors when we have situation like the queryreturning multiple rows but in this case select Count(*) from EMP table where EMPNAME='some' willreturn only one row, i.e how many rows it found withEMPNAME='some' eg:3 or 4 or 5 ... so there is no concept ofcursor here

What is copy book? Answer# 1 Copybook is the Facility provided to programmer for putting the variables or COBOL Code which is common to many programs. This helps programmer to optimize the code and able to write the error free code.

Copybook gets included into the program at the time of compilation from the PDS.

select * from orders where odate between '2010-01-01'and '2010-03-31' How do u fetch this into cursor? Answer# 1 Declare orders_cur cursor forselect * from orders where odate between '2010-01-01'and'2010-03-31'.

Open orders_cur

fetch orders_cur....

Hi friends i think that will the procedure ...if any mistakesuggest me

 

Write a query to retrive partial string. Answer# 2 A Sample Query:

SELECT SUBSTR(DESC_T,1,4) FROM PRODUCT WHERE PROD_I = 123456;

DESC_T is a column in the table PRODUCT

The first four characters of the DESC_T will be displ

CAPGEMINI

Suppose we are doing transaction in a table and abend happened in between. Suppose i have completed the transaction upto X rows and I want to start the transaction again where the abend happened. I don't want to do the transaction from the first record. Then what i have to do. Answer# 7 We have a option called the BMC dedicated for this kind of transaction errors. You can give a commit frequency too.

Example:

You have to insert 230 records into a table and your transaction abends at 163 due to -803 or some error.

Now, If you define your BMC commit frequency to be 100, a commit will be issued for every 100 records processed. So in our example the first 100 records will be commited and a checkpoint will be saved. When the transaction abends at 165 and you restart the job. The system will automatically look at the last checkpoint and start processing from 101 record.

Suppose we are doing transaction in a table and abend happened in between. Suppose i have completed the transaction upto X rows and I want to start the transaction again where the abend happened. I don't want to do the transaction from the first record. Then what i have to do. Answer# 4 we need to go for check point - restart logic.

HCLwhat is the difference between declaring the cursor in WS section and Procedure division? Answer# 1 As definition of cursor is non executable statement, it is advisable not to define in procedure division as it may misunderstood others.

we can define it any where only thing you have to take care is definition of cursor should be prior to the OPEN statement of the same cursor

Deloittewhat is the use of commit ? and what is the syntax? Answer# 2 commit is a command.It is used to make the changes permanenton the storage when the transaction is successfullyprocessed or execute.syntaxexec sql commit

end-exec.

Deloitte

Hi , I am posting some interview ques . what is the use of cursors ? what is the diff between select and cursor ? bith are used for data retrival .. rite? Answer# 1 >>Cursors are memory structures used to handle multiple rows at a time.>>Though,select statement can fetch only multiple rows, we cannot handle multiple rows using host variables.Basic difference would be single row handling and multiple rows handling.

How can we define a table? How can we apply SEARCH ALL on it ? Answer# 1 If u are asking about COBOL internal tables then it can be defined with occurs clause.

SEARCH ALL is the binary search in COBOL with only one WHEN Condition. Remember to declare the table with INDEX if you want to apply SEARCH ALL on it.

 

what is the steps followed in EXPLAIN Process or EXPLAIN command. (Explain is for identifying the optimized access path but how or what is the steps for doing the EXPLAIN) Answer# 1 In bind parameter if you give likeEXPLAIN(YES)

This will store the acess path in SYSIBM.SYSPLAN table,it will not do any optimization.

 

what is the steps followed in EXPLAIN Process or EXPLAIN command. (Explain is for identifying the optimized access path but how or what is the steps for doing the EXPLAIN) Answer# 4 Optimized access path means:When we are runnign any query database make the path to db2 for fetching the required data called asoptimized acess path.for creating optimized access pathdb2 uses db2 catlg.

expalain?

if put EXPLAIN(yes) then optimized path will sotred whatever query you written in the program in the table owner.plan_table

How to acess:

if you acess the owner.plan_table it will have the below imp coloumns.1.program name2.latest timestamp3.indexused4.and more details

TCSwhat is main use of table space and index object? please its urgent Answer# 2 >>Tablespace is one of the subdivision of Database in DB2.Tables are created in Tablespaces.Indexspace is also asubdivision of Database which contains indexes.>>One imp difference is tablespace can contain one or moretables whereas indexspace contains only one index for a table.>>Tablespace has to be created by explicitly mentioningcreate tablespace whereas indexspace is created implicitlywhenever we create an index using create index command.>> Index is an object which can be considered as pointers torows of a table.

What is the use of COMMAREA ?Minimum how much data we can pass from it? Answer# 1 COMMAREA is a communication area between COBOL and DB2. We can pass maximum of 32k. If it is more than that we can use the QUEUES

What are the abend codes occour in ur latest PROJECT in DB2 Answer# 1 -305 --->NULL value is moved to the HOST VARIABLE-803 ---->DUPLICATE ROW INSERTION-904 ---->DB@ Resources not avalable-805 --->PLAN not FOUNd-911/-913 -->TIME OUT DUE TO DEAD LOCK

What are the abend codes occour in ur latest PROJECT in DB2 Answer# 2 -803 ---->DUPLICATE ROW INSERTIONIdentify the record for SQLCODE=-803 and rectify the issue.-904 ---->DB@ Resources not avalableTry to use display database and check the locks on which pages.

DIS DB(MMLPDDDB) SPACENAM(MMLPD61T) LIMIT(*)-805 --->PLAN not FOUNdRebind the package/plan.-911/-913 -->TIME OUT DUE TO DEAD LOCK1. change the lock size to 'ROW' for all the tablespaces( tables) used in batch job program. here LOCKSIZE is a tablespace property.

2. do a RUNSTATS on all tabalespaces(tables) used by the batch program and mainly tables on which contention occured.

3. bind the batch program package/plan with isolation level CS and deallocate bind parameter as COMMIT.

What is the use of DELGEN in DB2? Can we Write the program with out using it? Answer# 2 DCLGEN(Declaration Genrator) is used to generate DB2 equivalent Cobol Datatypes. We can write a cobol Db2 program without using it but we will have to declare cobol variables in our Program and for sure it need to be DB2 Equivalent... but it is preffered to use DCLgen as it gives you the accurate... equivalent... of db2 datatype...and it enhances the performance no doubt...

For Sql Datatype is Name Varchar(20).so cobol equivalent is 01 Name. 49 Name-len PIC s9(04) comp. 49 Name-text pic x(2).

i have table agdet i want first 5 maximum commission which query will you write for this thanks in advance

IBMpls explain the precompilation process of cobol-db2 pgm Answer# 1 Precompilation separates the SQL statements and non-SQLstatements of an application program.From the precompilation step onwards the original sourcecode will be contained in two separate physical components.The non-sql part of the program will have all thesql-statements commented out by the precompiler and andreplaced with cobol call statements.The colection of SQL that was extracted will be placed in aDBRM(Database Request Module).Precompiler also places aconsitency token in both of its outputs.

How to handle null values in db2 Answer# 1 you need to declare binary indicator variable.... 10 Ind-variable pic s(904) comp.

how do u update the faltfile into db2 table ? Answer# 2 flat file is loaded to table by LOAD utility. BUTUPDATE with RRN value ,please any body elaborate

how can we retrieve the 100 records of the file ? Answer# 1 your qestion is not clear ??? first 100 rows or 100 rows any where ?? this is FIRST 100 row :SELECT * FROM TABLEFETCH FIRST 100 ROWS ONLYnow if u want retrieve 100 rows in any table, you have to create a counter ....

what is static sql? what is dynamic sql? Give me details Answer# 1 Static SQL is SQL statements in an application that do not change at runtime and, therefore, can be hard-coded into the application.

Dynamic SQL is SQL statements that are constructed at runtime; for example, the application may allow users to enter their own queries. Thus, the SQL statements cannot be hard-coded into the application.

what is + sign and -ve sign for sql code ? Answer# 1 In SQL CODE, + sign means warnings.In SQL CODE, - sign means unsuccessful execution.

: how can u understand the end of the table ? Answer# 1 If we set SQLCODE=100 means end of the table.

EX: if sqlcode=100 Display end of the table end-if.

where will u check for sqlcode ? Answer# 1 Be sure to include sqlca in working storage section.Then you can check sqlcode from the copybook variable SQLCODE.

 patnican i code union in cobol-db2 pgm ? Answer# 2 Sure you can...union is like to check the existence row in many differents tables...

: is there any restrictions for unions ? Answer# 1 yes, u have to code with the cursor.....

 

if i made sme changes in sub pgm wht r the things to be done for this ? Answer# 1 If that is static call, need to compile main program meanslink edit the sub program and main program in compiletime(create only one load module).

If that is dynamic call, compile sub program.

db2 restart logic ? Answer# 1 declare temp table with fields pkey and commit frequency.then set commit frequency ie after 100 records it shouldtabulate the temp table and in procerdure division read thelast pk value and should start updating after that pk valueand should delete this temp tble after the final commit orstop run.

what is sql abend code -801,-802? Answer# 1 first of all, i suggest you to read errors messages if you get any error before asking (always learning yourself) ...in db2 you have a lot...a lot error....nowsqlcode = -801, -802 it's mean you try to divide your column by zero....

i have one file having 100 of records? i want to display it like 20 records in one column and 20 records in another column total 40 records per page ? how ? Answer# 1 you can't do that in Spufi....You could do it in the

program cobol...you have to create 3 Pages...Each page you define 2 columns,each columns you define 20 records..

how to u check the query is executing or not ?where will u check for sqlcode ? Answer# 1 OH man...everytime, you execute a sql statement, u have to check you SQLCODE = 0 or not = 0....this a basic using DB2, Oracle....

how can u nderstand the sql stmts executed successfully or not ? Answer# 1 Oh man, you have to check SQLCODE for each SQL stament...SQLCODE = 0 it's good.... SQLCODE not = 0 it's bad.OR you look in SQLCA... verify this SQLERRD

 

i want to delete 20th million record how ? Answer# 1 Hope this will do.

delete from tbname where rowcount(*) = 200000 ;

 

i have a db2 variable how do u declare tht into working storage section ? Answer# 1 the db2 variable can be declared in the working-storage section by using the DCLGEN of the particular table.Eg:EXEC SQL INCLUDE DCB0XXXEND-EXEC.

cursor with hold explain ? Answer# 1 Hi All,Cursor is nothing but a pointer to a result set that you get as a result of executing a query.So if u wanna some operations using this cursor then u have to 1. Declare a cursor2.Open the cursot before the first access3.Do the operations 4.Close the cursor.

But in case of a normal cursor (not declared with 'with hold' option) in between the operations if u issued a commit command then the cursor will be getting closed and if u wanna access it again then u should open it again.

But incase of a 'With hold' cursor it will maintane the states if you issed a commit also.Means it will not get closed by applying frequent commit.

where can we use index and subscript ? with example ? Answer# 1 Subscript refers to the array occurrence while index is the displacement (in no of bytes) from the beginning of the array. An index can only be modified using PERFORM, SEARCH & SET.Need to have index for a table in order to use SEARCH, SEARCH ALL.This is cobol, not DB2..

select 100 records from million records ? Answer# 2 SELECT * FROM TABLENAMEFETCH FIRST 100 ROWS ONLY

Plan has the optimized access path in it. But is that optimized access path is attained or known Answer# 1 It is attained based on results from RUNSTATS utiliry. The Query Optimizer derives it.

 

Can we declare cursor in Procudere division or open the cursor in the Working storage section. Is there any particular rules in the usage of cursors lifecycle Answer# 1 yes we can declare cursor in procedure division but we cant open the cursor in working-storage section as it is an executabale statement.the lifecycle of cursor is as below 1>declare cursor 2>open cursor 3>fetch cursor 4>close cursor

Plan is an executable form of sql statements,But what exactly resides inside a Plan. what are the details inside Plan. Answer# 1

Plan contains DBRM(machine level SQL statements) and Optimized Access Path.

Plan is an executable form of sql statements,But what exactly resides inside a Plan. what are the details inside Plan. Answer# 2 . Plans are created by BIND command on sysibm.sysplan. To execute a package, you must 1st include it in the package list of a PLAN, so they are only executed when the plan in which they are contained executes.PACKAGEs are related to a PLAN, and PLANs are related to a COLLECTION-ID. So when we REBIND a Package we do not need to BIND REPLACE the Plan.

What is the specific need of Plan and..where are the tables in DB2 are physically saved Answer# 1 plan is nothing but executable form of sql statements and formed by one or more dbrms, it is used for run the program,without plan it will not run.

physically tables are stored in table space of data base.but the information of table is tabulated in system table 'sysibm.systable'

What is the specific need of Plan and..where are the tables in DB2 are physically saved Answer# 2 Though the tables are stored logically in tables space and physically all tables/table Spaces are stored in VSAM files....so the ultimate pgysical storage of any database object is a VSAM file.

wht is d/f b/w inner join and outer join ? d/f group by and order by having by ? Answer# 2 INNER JOIN: ALL THE MATCHED ROWS FROM BOTH THE TABLES AREDISPLAYED IN THE RESULTANT TABLE.OUTER JOIN: THERE ARE THREE TYPES OF OUTER JOINTHEY ARE1)LEFT OUTER JOIN2)RIGHT OUTER JOIN3)FULL OUTER JOIN

LEFT OUTER JOIN:- ALL THE MATCHED AND UNMATCHED ROWS FROMBOTH TABLES FROM THE LEFT TABLE ARE DISPLAYED IN THERESULTANT TABLESRIGHT OUTER JOIN:- ALL THE MATCHED AND UNMATCHED ROWS FROMBOTH RIGHT TABLE ARE DISPLAYED IN THE RESULTANT TABLE.FULL OUTER JOIN:- ALL THE MATCHED AND UNMATCHED ROWS FROMBOTH THE TABLES ARE DISPLAYED IN THE RESULTANT TABLE.GROUP BY CLAUSE:- IT IS USED ON GROUP OF DATA AND PROCESS ANSQL QUERY.ORDER BY:- IT IS USED TO SORT THE DATA IN ASCENDING ORDESCENDING ORDER.EX:- SELECT EMPID FROM EMP_TBL ORDER BY DSC (OR) ORDER BY 1.ORDER BY 1 i.e THE COLUMN NUMBER (EMPID)HAVING BY:- IT IS USED FOR CONDITION CHECKING.

 

how to delete perticular row from table for ex. how you will delete 3rd row from table please answer THANKS IN ADVANCE Answer# 4 delete from (SELECT * FROM (SELECT col1,rownumber() over() as rn from table where col =value) as tr where rn = 3)

 

i have a table like this : Name ADDRESS Toto 123 ... Toto 456 ToTo 678 I would like to delete 2 last row...please tell me how to delete its Answer# 3 delete from (select rownumber() over() as rowid from tab1 )where rowid = ( (select count(*) from tab1) - 1)

i have a table like this : Name ADDRESS Toto 123 ... Toto 456 ToTo 678 I would like to delete 2 last row...please tell me how to delete its Answer# 1 delete from table_namewhere address = '456' and '678

what are the bind parameters? Answer# 1 hi,

Bind parameters are:MEMBER - In bind package, LIRARY - DBRM library name ,ACTION(add/replace)- package or plan can be add or replace. ISOLATION - Determines the duration of the page lock.AQUIRE - Lock a tableon use RELEASE - releases when the plan terminatesVALIDATE - It will be check about authorization.

EXPLAIN - loads the access path selected by the optimizer in table

 

Is it Possible to declare or create a cursor for UPDATE of table? If yes tell me how? If no Tell me why? Answer# 1 yes, read this :Updating a column: You can update columns in the rows that you retrieve. Updating a row after you use a cursor to retrieve it is called a positioned update. If you intend to perform any positioned updates on the identified table, include the FOR UPDATE clause. The FOR UPDATE clause has two forms: • The first form is FOR UPDATE OF column-list. Use this form when you know in advance which columns you need to update. • The second form is FOR UPDATE, with no column list. Use this form when you might use the cursor to update any of the columns of the table. For example, you can use this cursor to update only the SALARY column of the employee table:

EXEC SQL DECLARE C1 CURSOR FOR SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY FROM DSN8810.EMP X WHERE EXISTS (SELECT * FROM DSN8810.PROJ Y WHERE X.EMPNO=Y.RESPEMP AND Y.PROJNO=:GOODPROJ) FOR UPDATE OF SALARY;

If you might use the cursor to update any column of the employee table, define the cursor like this:

EXEC SQL DECLARE C1 CURSOR FOR SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY FROM DSN8810.EMP X WHERE EXISTS (SELECT * FROM DSN8810.PROJ Y WHERE X.EMPNO=Y.RESPEMP AND Y.PROJNO=:GOODPROJ) FOR UPDATE;

DB2 must do more processing when you use the FOR UPDATE clause without a column list than when you use the FOR UPDATE clause with a column list. Therefore, if you intend to update only a few columns of a table, your program can run more efficiently if you include a column list. The precompiler options NOFOR and STDSQL affect the use of the FOR UPDATE clause in static SQL statements. If you do not specify the FOR UPDATE clause in a DECLARE CURSOR

statement, and you do not specify the STDSQL(YES) option or the NOFOR precompiler options, you receive an error if you execute a positioned UPDATE statement.

 

Cursors can be declared in both working-storage & procedure division, Agreed. But is there any difference? If could you please suggest what is the difference. TIA Answer# 5 yes there sre diffrence.If we declare cursor in working storage section then it is called as static cursor without parameters from cobol.

If we declare cursor in procedure division then we can use COBOL variables as parameters to the cursor. See the examples for both the cases...

Eg: STATIC CURSOR

WORKING-STORAGE SECTION. 01 HV-EMPNO PIC 9(4) value 7934. EXEC SQL DEFINE C1 CURSOR FOR SELECT * FROM EMP WHERE EMPNO=HV-EMPNO END-EXEC.

In the above case EMPNO is static. We cannot move a value or read a value in to empno, because it is in working storage section.

DYNAMIC CURSOR:

PROCEDURE DIVISION. ACCEPT HV-EMPNO. EXEC SQL DECLARE C1 CURSOR FOR SELECT * FROM EMP WHERE EMPNO=:HV-EMPNO END-EXEC.

In the above example we can read EMPNO from another table or file or we can take it from user. Now cursor becomes dynamic.

I hope now its clear for you. If you need more information let me know.

 

If I have 5 Queries in a DB2 Cobol program , while precompiling how many DBRMs will get created and How many Plans and Packages will get created while Bind Process? Answer# 1 when u bind , 5 queries has 5 sql statements in 1 DBRM, its regroup into 1 package, 1 plan...Plan is a collection of packages..correct me if i'm wrong..

could you give me an example how, where i code CHECKPOINT and restart...I need and example....thanks.. Answer# 1 You should pass CHECKpoint frequency value from JCL to cobol program.Intern cobol program will have the table of retart logic.Table contents(coloumns)be: 1.No of records ,2.No of records + 1, 3.no of records processed etc.Once the updattion or insertion got stucked while processing ,All the relative data will be stored the above mentioned table.

So check the record from table .Fix the abend and restart your job for the failed step.

This is mainly production support work .manually u have to check the record .and get the records info from the table and restart the job from the failed step

Can you search give an array in the WHERE clause of a db2 query? Answer# 1 arrays are not supported by sql so trying to add an array ina where clause of sql may throw some errors...only dot operators are possibly used...

plz correct me if i'm wrong

Can i insert bulk records into a db2 table using qmf of spufi only............!!! Answer# 1 Thru SPUFI/QMF/FileAID you can insert bulk records by selecting the records from one table and inserting into other one.

I have some 3 particular fields ..i want to know which all tables have those 3 fields. Is there any way to identify.. can we know by quering system tables.. Answer# 1 select * from sysibm.syscolumns where name = <name you 3 columns here>

The above query to metadata will show you the list of table names where these 3 columsn present

when we are tying to update a table having 100 rows. if the program abends when updating 51 row . how to start updating again from the 51 row .what was the logic Answer# 1 when we are tying to update a table having 100 rows.

if the program abends when updating 51 row . how to start updating again from the 51 row .what was the logic

Ans: The Possible answer would be..if you had used COMMIT before 51st ROW .. the Former recordswould have been updated in the table .. If No COMMIt was used.. The whole transaction would have been ROLLBACKED.

Now If you want to start a fresh Transaction and want to start Updating directly from 51st Row Then There are two ways

1> Perform a loop to scroll till u have read 50 rows Then Point ur cursor as CURRENT to the 51st Row Start Updating the Records Till end of table.

or

2> Declare a Scrollable cursor & use FETCH ABSOLUTE option to fetch a particular row directly

EXEC SQL FETCH ABSOLUTE +51 C1 INTO :TEMP1, :TEMP2, :TEMP3;

this statement fetches the 51st row of the Table

Bind concepts in DB2 cobol Answer# 1 The first thing is Host languages.Whatever we are using in cobol (other than cobol langauge command) that are called HOST language.

DB2 is also one of the host language.COBOL compiler does not know the host language and does not compile the same.we will take cobol-db2 program..Here, we are introduce PRE-COMPILER....Pre-compiler will spilt the cobol db2 program into two module.1. Cobol program (fully cobol,all the host language commands will get replaced with "MOVE and CALL" statements.2. DBRM (DataBast Request Module)- only those commands, which are code within 'EXEC SQL .. END-EXEC.

Now, we have spited and we have separate for each..(COBOL and DB2)..We know about compilation process for COBOL.Now come to BIND process....Bind is nothing but, compilation process of DBRM.The output of this compilation process(BIND) is Package.

If we bind the packages then we will get Plan/Application plan.When we do the link-edit the cobol program, a thread will be created between the load module of cobol and plan.

what is the difference between normal select query and currosor Answer# 2 using select in embedded sql with where clause should fetchonly one row , but cursor can be used when we need more rowsto be retrieved one at a time.

In case more than one row is retrieved in a select clause itwill throw -811 sql error.

When DB2 connection fails, will online program work or give errors. Answer# 1 Online program will not work and it will give you abend AEY9 when retry to use if connection has not been established.

what is Static and dynamic linking Answer# 1 In static linking the load modules of the called/sub programs are also loaded into main/calling program load module,and incase of dynamic linking the load modules of main/calling and sub/called programs are kept seperate and will be loaded dynamically when ever there is a perticular call statement.

Normalization in DB2 – first normal form, second normal form Answer# 2 First normal form (1NF) lays the groundwork for an organised database design:

• Ensure that each table has a primary key: minimal set of attributes which can uniquely identify a record.

• Eliminate repeating groups (categories of data which would seem to be required a different number of times on different records) by defining keyed and non-keyed attributes appropriately.

• Atomicity: Each attribute must contain a single value, not a set of values.

• 'First normal form' depends on functional dependency formula f(x)=y. For every value of x there is value for y.

What is reorg in DB2 Answer# 2 Reorg is needed whenever indexed columns are updated frequently.

When indexed columns are updated frequently, then the indexfor the table has to be created again.

So REORG utility is run which will drop the index and createthe index again for the table.

What is image copy

What is Reorg Recovery? when will you run it? Answer# 2 reorg is a db2 use to improve the performance and any unusedpages in table space is rearange it perticul order andrelease free space//job1 job....//step1 exec pgm=IKJEFT01//sysin dd * reorg db123.tb123//

What is PRIME TABLE and what are the uses of it Answer# 1 Prime Double Hash Table always uses prime number table sizes. The table is expandable, and the cost of rehashing is amortized across each insertion or deletion operation

how to resolve -818 error. how to see timestamp token in load module and plan Answer# 1 BIND the application again, using the DBRM for the application program that matches the object module.In load module we can check timestamp.for timestamp token Plan, check for BOUNDTS in SYSIBM.SYSPLAN

when we are trying to update db2 rows. if the program abends . how we will know that the last successful update row was Answer# 3 In SQLCA , we have field named SQLERRD . In SQLERRD(3) , theno of rows successful updated (or) inserted can be found .

when we are trying to update db2 rows. if the program abends . how we will know that the last successful update row was Answer# 1 We can go in Abend Aid and look for the source listing wherewe can find the last values stored in Host Variables wherethe Job Abended.

how to resolve -805 . how to see dbrm and package not found Answer# 1 I think u have to recompile and bind your program, then look at jcl compile and bind , locate the package name AND !!! GO TO SEE your DBA....

how to resolve -811 error. give clear explination Answer# 1 When a Select query orSubselect query returns more than one row the sqlcode -811 is returned RESOLVE: DELCARE CURSOR

how to resolve -811 Answer# 1 Declare cursor instead of singleton select,as the query you are running has multiple records.

Why PS file called Physical seqential file?How to sore data in sequential file and ESDS file? What is the use of DBRM,PLAN,package and Collection. Answer# 1 What is the use of DBRM,PLAN,package and Collection.??

DBRM is just a SQL/DB2 part of a embeded COBOL and DB2 program,basically DB2 Precompiler creates this by just commenting out all SQL/DB2 part.

PLAN is a collection of packages and its an executable for DB2 system just like a loadmodule for Cobol.Plan stores all the accee paths for table/database.

Package is a smaller version of package,we can say its a bound DBRM or a plan for a single program,but its not executable by itself,we can to bound it into plan,so that plan can be executed.

We can directly have PLAN instead of going to package,but we have many advantages of using package over the plan as below.

1.We can have seperate versions(like same DBRM can be bound into test package,model office package and production package)

2.more granularity i.e when ever we change some sub routine,we no need to bind all the calling/called chain and block the entire transaction,we can just rebind the perticular package so that the plan while executing automatically picks it up the latest version of the package.

3.involves less cost,use the above said example.

4.easy maintanance.

COLLECTION

Collection is just a logical name given for a set of packages that can be sed while creating the plan....there is no physical existance of a collection...its a matter of convenience to the user.

Hope this answers your query on DBRM,plan,package and collection,if not let me know.

 

Can a array declared with an index be displayed(readable format) in spool? Answer# 2 Yes we can display like below

Display Array(Idx)

 

how to take the back up of the DB2 table. Answer# 3 In JCL, We can Use DSNTUAIL Utility to downLoad the Tables in to Sequential Dataset.we have to Wirte the Program (DSNTUAIL) and it's Plan in to SYSTINAnd write the Query into SYSIN clause

how can we find nth max salary from A table Answer# 3 select * from table1 a where (n-1) = (select count(distinct(sal)) from table1 b where b.sal>a.sal)

how can we find nth max salary from A table Answer# 2 select * from table1 a where n = (select count(distinct(sal)) from table1 b where b.sal>a.sal)

n is value of n it should be numeric....

 

in the CURSOR declare statement, if i am using an where clause and trying to compare the value with a host-varialble. EXEC SQL DECLARE C1 CURSOR FOR SELECT DEPTNO, DEPTNAME, MGRNO FROM DEPARTMENT WHERE ADMRDEPT = :ADM-DEPT Where would i need to provide value to this host-variable in the where clause. how could i pass value to this host-variable to check it. Answer# 1 Before opening this cursor thru OPEN <cursor_name>. becausewhen we open the cursor, it fetches all matching recordsfrom table and stores them in buffer. so it evaluates thatwhere clause while opening the cursor.

a cursor normally gets closed once we provide a commit . If u try to close the same cursor with close cursor command later after providing the commit will there be any sql-error. Answer# 1 when we try to close the cursor that is already closed, itwill return the SQL error code

-501 - cursor found in the fetch or close statement is not open.

i hav created a program and moved to production but failed to create the table which is been used in the program. the program shows error as soon as it is moved or installed in production or shows error during its runtime. what is the sqlcode for this error Answer# 2 The code promotion will fail. When you promote the code from one region to other (say QC to Production), the program will be bound (BIND) to the new DB2 environment. The BIND will fail with SQLCODE of -204 (Object Not found).

The code will not be moved to production successfully and there is no chance of 'runtime' error.

if there is a table with huge number of records and if i want to extract only first 3 records from the table, what query i have to provide to retreive first 3 records Answer# 2

SELECT * FROM <qualifier>.<table_name> FETCH FIRST 3 ROWS ONLY WITH UR;

The above query holds good, if you are thinking to fetch rows in any specific order. Otherwise make use of "ORDER BY".

If you want to filter the records bases on some condition and fetch first 3 rows, make use of WHERE clause. For example, if you want to see first 3 records of ACCOUNTS department, use WHERE DEPTID ='Accounts' FETCH FIRST 3 ROWS ONLY

What are db2 error codes -305 and -407. What is the difference between these 2 error codes ? Answer# 1 Hi,-305 is Null Indicator is needed. The null value cannot beassigned to output host variable. Because no Indicatorvariable is specified. (to fetch or select operationresulted in the retrieval of a null value to be insertedinto the output host vaariable)

-407 is An update or insert value is null. but the objectcolumn name cannot contain null values.(the update or insertvalue was null. But the object column was declared as notnullin the table)

if suppose i am having an ACCOUNT table with 3 coloumns ACC. NO,ACC. NAME,ACC. AMOUNT . If a unique index is also defined on ACC.NO and ACC.NAME then write a query to retrieve account holders records who have more than 1 ACC. Answer# 1 select distinct ACC.NO, ACC. NAME, ACC. AMOUNT

from account where acc.name in ( select distinct acc.namefrom account group by acc.namewhere count(acc.no) > 1)

What is the purpose of coding FETCH in a SQL SELECT query? Explain with an example in Detail? Answer# 2 FETCH is used to retrieve a specific row from a result set of the SELECT query and places them into local variables.

 What is the purpose of the SDSNLOAD dataset in the STEPLIB while running DB2 programs? Answer# 1 THE SDSNLOAD load library allows the batch job to access the DB2 load modules.

How to resolve -504 sql code in DB2? Answer# 1 Hi,-504 is cursor not declared error.may be cursor name misspeled also coming this error.

 

: How to resolve -504 sql code in DB2? Answer# 2 There are following possibilities to this issue to come,

1) Cursor is not declared in application program or in stored procedure depending on where it is used.

Soln - Declare the cursor in application program or in stored procedure depending on where it is used.

2) Cursor spelling is not correct which you are going to open or fetch.

Soln - Check the name used and correct.

How to resolve -407 sql code in DB2? Answer# 4 This error occures when you are assigning NULL value to NOT NULL column.You can wrap the variable with coalesce/value and default it but it is totally dependant on business logic.

How to resolve -917 sql code in DB2? Answer# 3 Re-bind the package and plan.Compile and run the pgm

 

How to resolve -502 sql code in DB2? Answer# 2 This error occures when you are opening the cursor which is already open in current session.

How to resolve -818 sql code in DB2? Answer# 1 its due to time stamp mismatch .Bind the job once again and

link it

How to resolve -803 sql code in DB2? Answer# 1 This error is thrown when you try to insert a row with existing primary key i.e.Duplicate. If you want to insert the row either you should delete the already existing row and insert the new row or update the same row with the new values for other fields

How to resolve -805 error in DB2? Answer# 1 Reason for Sql Code -805: The application program attempts to use a DBRM package that is not found ('location-name.colection-id.dbrm-name.consistency-token').

Solution: 1. Rebind the program with the correct DBRM library name. 2. The plan name used in the JCL and the plan used for bind should be same.

I have 3 cursors declared. Cursor1 retieves some data. Based on this curso2 will also fetches some data. In cursor3 (using for some updation) I'm using the data retrieved by the above 2 cursor. My question is, while working with cursor3, periodically if I give commit, will all the three crsors will be closed or only cursor3 will be closed? Answer# 1 COMMIT will close all the cursors, if the cursors are not delcared using 'WITH HOLD'.

 

How to define the a field which accepts value till 99999.99 in db2 Answer# 2 in db2 decimal(7,2) & application cobol pgm 9(5)v99.

For unmatched rows returned from an outer join, the column values in the other table are set to null e.g If A OUTER JOIN B returns some unmatched rows from A, the corresponding column values in B will be set to null. What can be done so that a null value is not displayed for these columns? Answer# 1 Use the COALESCE function

Suppose the outcome of executing a query results in a row having null. Based on the answer how you use it? Its declaration and inclusion? Answer# 1 While declaring the cursor include the null indicator for

the variables which you are retrieiving from the table.

Example.

Select empno,deptname from emp into:ws-empno:ws-empno-ind,:ws-deptname:ws-deptname-indwhere empname='....'

Based on the values in ws-empno-ind,ws-deptname-ind we canknow whether the vaules being retrived are null are not.If null indicator variable has a value -1 then the retrieved value is null.

If you do no specify null indicator variable and the valueretreived is null then you would get -305 as the sqlcode.

The null indicator is declared as 05 ws-empno-ind s9(4) comp

Is it possible to update a primary key value? If not, what is the error code given? If yes, can more than 1 primary key column be updated at a time? Answer# 3 Primary key value can be updated as well provided the value you update it with identifies it uniquely.

Is it possible to update a primary key value? If not, what is the error code given? If yes, can more than 1 primary key column be updated at a time? Answer# 4 Ans 3 is correct. If the key already present, while updating it will show the sql code as = -803.

Is it possible to update a primary key value? If not, what is the error code given? If yes, can more than 1 primary key column be updated at a time? Answer# 5 further, multiple primary keys can be updated at time. Only condition is that value should be unquie. else sql code -803

Is it possible to update a primary key value? If not, what is the error code given? If yes, can more than 1 primary key column be updated at a time? Answer# 6 also remember, you cannot update a primary key when it has matching foreign key values.

Can a unique index have more than one null value? If not, what error code is given if an attempt is made to insert more than one null value? Answer# 2 Unique index can have null values and no null value is equalto another null value.

i don't know if it raises an error or not when you insert anull value.

Null value acceptance in the column is again based on thedefinition given for the column whether it can accept nullvalues or not.

Can a primary key have null values? If we try to insert a null value in a primary key column, will it work or give an error code? Answer# 4 The PRIMARY KEY cannot be null. you can insert ZERO into that but should not be null. It will throw an error if you try to inserts a NULL.

 

Can a primary key have null values? If we try to insert a null value in a primary key column, will it work or give an error code? Answer# 6 In DB2 you can not create a primary key on a column which is not 'not null'.If you are trying to create a primary key on a column which is not 'not null' then dbb2 will return an error like this

SQL0542N "feild_name" cannot be a column of a primary key or unique key because it can contain null values. SQLSTATE=42831

But once u alter the table and set the column to 'NOT NULL' then you can make it as primary key.

Now if u r trying to insert an null value to ur primary key column then db2 will trows an error like

SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=5, TABLEID=404, COLNO=0" is not allowed. SQLSTATE=23502.

In short you can not make a column which can hold 'NULL' value as your primary key and you can not insert a 'NULL' value into your primary key feild.

What is the maximum length of a column name in DB2? 18 or 30 or anything other than these? Answer# 2 it depends :) which vesion of DB2 that you are referring to...

V7 : 18V8 : 30

In BIND, isolation level parameter specifies the duration of page lock and ACQUIRE, RELEASE also do almost the same thing. What is the exact difference between the two? Do they work in conjunction while executing SQL queries and obtaining locks? Answer# 6 (1) Isolation specifies type of lock that should be used(2) Acquire tells when the lock should be acquired(3) Release tells when it should be unlocked

 

Is it Possible to read from PS file and write it to database by using dynamic sql(execute immediate) Answer# 2 Using the Dynamic sql the preparation of bind-plan will be costly. As you know the data it is advisable to use the static sql directly.

Dynamic sql is also valid.

 

What is package versioning? Please give an example. Answer# 1 Versioning allows multiple packages that share both theschema and Package ID to co exist in the system catalogs.

A sample application called payroll is compiled frompayroll.sqc.This application would be precompiled and thepackage payroll bound to the database and the applicationdelivered to the users.users then could run the applicationin production.If the application required modification (even without sqlchanges) the process of recompiling,binding and sending theapplication to the users must be repeated.If the versionoption was not specified for this second precompilation ofpayroll, the first package is replaced by second package.Any user who attempts to run the old version of applicationwould get -818 error.To avoid this use VERSION and it allowsboth versions of the appliction to run at the same time.

When the first version of Payroll was created, the versionoption should be used during the precompilation step.

DB2 PREP PAYROLL.SQC VERSION V1.1

why db2 is called as universal database?what is the storage capacity of db2v9 n what are the advantages of db2 Answer# 1 IBM developed the RDBMS that can be used in any platform.

Say in Mainframe, Windows, Unix. That why it is so call UDB.

Regarding the space I'm not sure.Advantages:1) Builtin security. (Like Grand&Revoke)2) It provides logical & Physical data independency.3) You need not open& close as what we are doing in files.4) Sorts will be internally performed at DSN07DB. 5) Recovery will be easy(Using the utility)6) you can also create View which are called virtual tables. This also give greater security on your table.The above mentioned are few advantages of db2.

What is the difference between Primary key and unique index? Answer# 4 Primary Key is Unique and Not Null. Unique index can be defined on Primary Key or any other Unique column in the table. Unique index can have a Null Value unlike Primary Key.

If a table has a column "dept" (declared to have nulls) and there are 10 rows in the table of which 3 have a null value in the dept column, what will select count(*) and select count(distinct dept) return? Answer# 1 SELECT(*) Will return 10 and SELECT (distinct dept) will return 8.

If a table has a column "dept" (declared to have nulls) and there are 10 rows in the table of which 3 have a null value in the dept column, what will select count(*) and select count(distinct dept) return? Answer# 2 select count(*) returns 10 --- this is right. But,select count(distinct dept) returns 7. Because null values are not considered as distinct.

Consider that a package is bound into 2 different collections and the PKLIST option specifies both the collections. If the collection id is not specified in the program while executing the SQL query, then when the DB2 system will search the package list, which collection will it pick up or will it give an error/abend? Answer# 1 Generally when the search is done the order in which thecollection id is specified in the list would be taken forpriority.

You can use the special register CURRENT PACKAGE PATH orCURRENT PACKAGESET (if CURRENT PACKAGE PATH is not set) tospecify the collections that are to be used for package

resolution.The CURRENT PACKAGESET special register contains the name ofa single collection, and the CURRENT PACKAGE PATH specialregister contains a list of collection names.

 

What is the SQL query to select, delete and get count of duplicate rows in DB2? Answer# 1 DELETE FROM ( SELECT ROWNUMBER() OVER (PARTITION BY c1, c2 ORDER BY c3DESC) AS rnum FROM t1) WHERE rnum > 1 ;

What is Call attach facility? How does it work? Answer# 1 Call Attach Facility is used for application programs thatrun under batch to establish a connection with db2.It is analternative to DSN command processor and provides greatercontrol over the execution environment.

Programs using CAF can

1)Access DB2 from address spaces where there is no TSO,CICS, or IMS2)Access the Instrumentation Facility Interface (IFI)3)Access DB2 from multiple tasks within an address space4)Control the connection to DB2 explicitly5)Connect to DB2 implicitly, using a default subsystem IDand plan name6)Receive a signal from DB2 on start-up or shutdown.

The Call Attach Facility is being slowly replaced by themore powerful RRS (resource recovery services) attachmentfacility.

If we keep the DCLGEN structure for a table in a copybook and include it in the COBOL program using the COPY statement, will there be any impact during compilation or at any stage of program execution? Answer# 2 DCLGENs are processed by Precompiler.

So instead of using COPY "DCLGEN Menber", it is required to use INCLUDE "DCLGEN member".

This is because INCLUDE copybooks are being expanded during Precompilation and COPY "Member" will be expanded during compilation.

If a non-DB2 program calls a DB2 program, the calling program's name will be there in SYSIN of IKJEFT01 and the plan name will be that of the called program. But is a bind needed or a plan has to be created for the non-DB2 program also? Answer# 1 No Plan needs to be created for the Non-DB2 program. The SYSTIN should be as below:

DSN SYSTEM(XXXX)RUN PROGRAM(Non-DB2 Program name) PLAN(DB2 PROGRAM NAME)END

Can SQL statements/queries be included in a copybook? Answer# 1 Yes, if the copybook is included with the precompilerdirective EXEC SQL INCLUDE. No, if one attempts to includethe copybook with a COBOL COPY directive.

Generally if I want to select the names starting with c I need to use c% But how could I code to select the data which contains % as a part of data. Answer# 1 Use '%%' to select the data which contains %

When you are working with the project after coding what will u do? Like what type of documents u will prepare? How will u do testing?

What value the host varible will contain , if null indicator value is -2 ? Will it contain the truncated value or nothing will move ? Answer# 1 That Host Variable contain the Truncated value when Null Indicator conatin value -2.

What if we try to insert the base table through updatable view , but failed to give a column value which is defined as NOT NULL. Answer# 1 get error code "-407"

-407 :- An UPDATE, INSERT or SET VALUE is NULL, but the object column defined as NOT NULL values.

What if we try to insert the base table through updatable view , but failed to give a column value which is defined as NOT NULL. Answer# 1 get error code "-407"

-407 :- An UPDATE, INSERT or SET VALUE is NULL, but the object column defined as NOT NULL values.

 

How Plan is created while executing the query using SPUFI? Answer# 1 No Plan is get created while executing the query using SPUFI. SPUFI , QMF are only tools to check the correctness of your query , i.e where ur query is correct or u have coded anythig wrong .

What if , we failed to mentioed null indicator in sql select query , that may retrieve null value ? Answer# 2 Sorry for the above answer :(

Please see the Correct answer, we will get the SQL error -305

-305:- The NULL value cannot be assigned to output host variable number because no indicator variable is specified.

What if we fail to give values in columns declared as NOT NULL ? Answer# 1 we will get the sql error code "-407"

-407 :- An UPDATE, INSERT or SET VALUE is NULL, but the object column cannot contain the NULL values.

what is differnece between DROP TABLE & DELETE TABLE . Answer# 3 in DROP TABLE command it will remove the table containts as well as table structure from the database catalog, also it release all the storage space it has acquired previously.we can't rollback it as it is a ddl command.

but in case of DELETE TABLE command it just delete all the records but not relaese the spaces it acquires .it can be rollback as it is a dml command.

1. what if null values retrived from database and no null indicator mentioned in query. What is sql code. Answer# 1 we will get the sql code "-305".

-305:- The NULL value cannot be assigned to output host variable number because no indicator variable is specified.

Can we able to find all the Table names under a Particular Plan? Answer# 1 Absolutely! Just look at the catalog table SYSIBM.SYSPLANDEP

It's pretty straight forward! It recrods the dependenciesof plans on tables, views, synonyms, table spaces, indexes,aliases, functions and stored procedures.

My cobol program(not having any sql statements) is calling another cobol program ( having sql statements), what is needed for compilation and run in jcls . Answer# 1 Hi,

Lets consider the name the calling prg. as PROG1 and called prg. as PROG2, Pgm PROG1 should be compiled as a normal CBL pgm, and Pgm. PROG2 should be compiled as a CoBoL-DB2 pgm. And the Run JCL should call the PGM PROG1, using IKJEFT01 pgm, since it calls pgm PROG2 with SQL statements. For the JCL u can refer to the JCLS link in the same website

What error I will get if my select query gives multiple row while executing a Cobol-DB2 program.How can I solve it without using a cursor. Answer# 1 -811 ERROR CODE

 One program calling 5 subprograms, its a cobol db2 program, after precompilation how many plans and packages will created? Answer# 1 when u precompile a Db2 program you will get (n+1) DBRMs where n indicates no. of subprograms. So in our case you will get 6 DBRMs. Through Binding process you can get minimum of 1 package and maximum of 6 packages. But finally you will get only one plan.

we can code COPY DCLGEN or INCLUDE DCLGEN, At which stage of the precompilation , dclgen get expanded if we write 1) copy 2) include one question about dclgen.. Is it mandatory to use declare table in dclgen.. I think no...but it will

be used by the precompiler to validate the table name,column name etc.., can one clear my doubt ..is it necessary to include declare table or not? Answer# 2 During the precompilation time only copybooks mentioned with INCLUDE are expanded all copy books mentioned with COPY are expanded during compilation timewe have to specify all the dclgens and host variable declartions using INCLUDE statement because those are required during precompilation period.

Declare statement is not mandatory in the DCLGEN.it can be used to validate the sql statements in the program i.e it checks the table name and columns names and it gives the corresponding warning messages.

 

What techniques will be used to avoid deadlock?? Answer# 1 -Minimize the lock size as much as possible.- Maximize the sharing of the resource by using CS or UR Isolation levels when possible.- Minimize the number of rows in one page for frequently used tables if you want to avoid row locksize.- use CURRENTDATA( NO) to avoid acuiring locks.

 

How to get Top 10 Salaries from a Table Answer# 4 The query will be like this:

Select distinct salary from tableorder by salary descfetch first 10 rows only

 can we drop column from a table Answer# 1 We can not drop a column. If we want we can append colume with ALTER command.

How to know the primary key of a table without defining the primary key in the table? Answer# 2 you could use SYSIBM.SYSINDEXES table to know more about the table index.

What are the functions in DB2? Answer# 1

the following functions ( Aggregate functions )applied on the columns.

CountAvgMinMAx....

I have a table which has thousand of records i want to fetch only record num 100 to record num 200. Write a query that satisfies this criteria.(Cant use any keys) Anyone please reply ASAP!

I have a table which has thousand of records i want to fetch only record num 100 to record num 200. Write a query that satisfies this criteria.(Cant use any keys) Anyone please reply ASAP! Answer# 13 YA THE ANSWER IS BY USING CURSORS ONLYdeclare cursor USER_Cursor is select * from <table_name>; y <TABLE_NAME>%ROWTYPE; --ITS A DATA TYPE INCLUDES WHOLE ROW 4M A TABLE IN TO X COUNT1 number(2); begin COUNT1:=1; open USER_Cursor; while USER_Cursor%FOUND AND COUNT1 <>101 loop fetch USER_Cursor into y;-- JUST FETCH DONT DISPLY ---TILL 101ST RECORD COUNT1:=COUNT1+1; end loop; --NOW DISPLAY FROM 101 RECORD TO 200 RECORD while USER_Cursor%FOUND AND COUNT1 <>201 loop fetch USER_Cursor into y; dbms_output.put_line(y); COUNT1:=COUNT1+1; end loop; close USER_Cursor; end;

 select * from emp where rownum<=100 and rownum>=200;

quary for 1> fetch last record 2> fetch in reverse order 3> fetch last 5 row 4> fetch second last record (their is no primary key) i think concept of count can be use for this in 4th Answer# 5 1) select id,name from tab1 order by id desc fetch first row only2) select id,name from tab1 order by id desc3) select id,name from tab1 order by id desc fetch first 5 rows only4) select id,count(*),name from tab1 order by id where count(*) = (select count(*) from tab1)-1

 

why we create view. Answer# 1 I know a senario in which views are created.

Suppose I have a field in my table which is secure and should not be visible to everyone. In that case i can create a view without that field and keep the table acces (even browse) with a particular set of people.

Can we use select * statement in cobol program without giving field names ??? Answer# 2 Yes we can do it but as a practice people don't use "SELECT *..." in COBOL program. Possibly the main reason for this is that if there is any modification to the table in future (e.g. a column addition) then all such programs needs to be at least recompiled once even if there is no code changes into them. This is ensure taht they use latest DCLGEN for the modified table else the programs will abend for column mismatch.

how to fetch multiple records without using cursor Answer# 9 it is not possible in db2 v8 : you still need a cursor.However : you can fetch 100 rows in one fetch.

01 host-variables. 05 HV-col1-ARRAY PIC s9(04) COMP OCCURS 100 TIMES. 05 HV-col2-ARRAY PIC x(40) OCCURS 100 TIMES. 05 HV-vcol3-ARRAY OCCURS 100 TIMES. 49 HV-vcol3-len-ARRAY PIC s9(04) COMP. 49 HV-vcol3-txt-ARRAY PIC x(40). 05 HV-IND2-ARRAY PIC s9(04) COMP OCCURS 100 TIMES.I

DECLARE csr1 CURSOR WITH ROWSET POSITIONING for select col1, col2, vcol3 from ...

open cursor

FETCH NEXT ROWSET FROM csr1 FOR 100 ROWS INTO :HV-col1-ARRAY, :HV-col2-ARRAY:HV-IND2-Array, :HV-vcol3-ARRAY

 

what is datacom db? Answer# 2 DATACOM/DB was a database management system of the inverted file type.[1] Acquired by Applied Data Research, it is now owned by Computer Associates and is sold under the name of CA Datacom.

A DB2 application is bound with uncommitted Read isolation level.It issues a request that retrieves 20 rows out of 200000 in the table. Which of the following descrbes the rows that are locked as a result of this request? a. None of the rows are locked. B.The retrieved rows are locked. C.The last row of the result set is locked. D.The rows not previously updated by another application are locked. Answer# 1 a. none of the rows are locked.

Thanks;Amita Sharma.

How we create a tables in db2,what is the process/syntax to create a table with example plz? Answer# 1 Its very much similar as we sue in other DB

Syntax: CREATE TABLE EMPLOYEE(EMP_ID CHAR(10), NAME VARCHAR(20))

The syntax is usful only in command centerBut from GUI its more easier to create table

 

How we create a tables in DB2 ?Previously client has using 7 letters for user id,now he wants to increase the letters from 7 to 12 for user id.How we can do it? Answer# 2 i think, the size of variable is not possible to change,please correct me if i am wrong.

so for the solutionwe can creat new table with change of the size of user_id char(12)

and if you want the same name of tablestep1 >creat a table for interface perpose with user_id char

(12) 2> insert all the data in the interface table 3> delete the main table. 4> create table with same name of main table with user_id char(12) 5> insert data from interface table to main table.

how many partitions are possible in partitioned tablespace? dont say 1-64. a)240 b)256 c)512 d)none Answer# 5 Unfortunately nonoe of the above answers are completely correct. Answer posted by Nawal Kishore is partly correct but not the complete answer. Answer from Vani may be partly correct depending upon the DSSiZE and the page size. Let me try to answer the question:

DB2 alllows a maximum of 4096 partitions of a partitioned table space. Actually, the maximum number of partitions in a table space depends on the data set size (DSSIZE parameter) and the page size. Thus the partitions can range from 1 to 4096 depending upon the above parameters (DSSIZE ands the page size).

You can refer to IBM documentations to confirm the same...suggesstions are welcome...!!

 

Re: In a single table,How to retrieve a employee-id of an employee who works in more than one department? Answer# 4 Select emp_idfromemployeegroup by emp_id,depthaving count(*)>1;

# 5 hi all i hope this answer will help you

empeid ename dep001 manikandan mainframe002 karthi java003 jarin mainframe001 manikandan java003 jarin testing004 hariharan java

001 manikandan testing

select eid from emp e1 where 1 < (select count(*) from emp e2 where e1.eid=e2.eid)

How can you split a table in to exactly half? Answer# 1 Hi,

Here i am giving the procedure , how to make the table into half..... using program... I dont know whether any direct SQL command is there or not. This is just for ur idea what i know.

1) Find the total number of rows in a table using count(*)

2) Declare two variable in working storage section.eg: 02 A pic 9(4) . 02 B pic 9(2).

store the count no into one variable .assume u stored that in A.

then divide that by 2. so u will get half of the rows in a table. strore that value in B.

3) Take another variable name C .

eg: 02 c pic 9(4) value 0.

perform para1 until B =C

para1:

Retrive the row from the table.

move to corresponding host variables ( use table to store muliple rows ).

insert into table(u can insert multiple rows at a time ...)

do the same for storing the other half into the another table.

means here 1 st half rows were stored in one temp table .... the other in the one table.

: How can you split a table in to exactly half? Answer# 2 i forgot to mention the c value increment in the above mail.increment the c value after each row retrieved.

 

if one db2 pgm calls another db2 pgm. how many plans do we need. what r the steps involved.. Answer# 1 Hi buddy,

one plan is reqired,,create 2 dbrms for two programs

steps

precompile with dsnhcob2ikjeft01create dbrm, then in next step using same ikjeft01 create plan with this 2 dbrrm

pls let me know if u have any queries

what are extents? Answer# 2 EXTENTS ARE CONTIGIOUS MEMORY ALLOCATIONS

FOR SEQUENTIAL 16 EXTENTS ARE POSSIBLE AND FOR VSAM 123 EXTENS ARE POSSIBLE..........

How to update more then one record using update? Answer# 2 For this concern you can use or oprator along with where clause, Example

WHERE DEPT_ID='D110' OR 'D001' OR 'D105'

 

How to insert more than one record using insert? Answer# 1 Do an Insert using selectInsert into Table1(coltable1, coltable22)(select coltab21, tab2col2 from Tab2 where <condn>)

 

what happend with out using Commit,when closing cursor and program? Answer# 3 yes, commit-it will save the changes that we done to db2 table

when operation is performed successfully table will be auto committed its default,suppose if any abends occur all changes will be rolled back , table will come to original status

since while updating millions of records somewhere some abend occurs that time its better to have commit ,so till upto abend whatever changes occured in table will be

saved.....

What does this mean ? GRANT BIND,EXECUTE ON PLAN MK2 TO PUBLIC; Answer# 1 Granting BIND gives BIND authority on the Plan MK2 to all users. BIND PLAN establishes the relationship between DB2 and all DBRMs or packages in that plan.

BIND(ADD) can be used for creating a new plan or package.BIND(REPLACE) can be used when the program has been changed.

With this authority, any user is able to bind a DBRM or a package to the plan MK2. The users can rebind the Plan as well.

The Bind process performs the following :1) Checks SQL syntax.2) Checks security.3) Compares column and table names against the DB2 Catalog.4) Builds an access path strategy for each SQL statement.

Granting EXECUTE to PUBLIC gives all users, the authority to EXECUTE the plan MK2.

A Table feild is declared as Decimal(7,2). I want to insert the decimal in to this column thru cobol-db2 program. How should I declare my local input file variable or any suggestion? Answer# 4 u can declare in application program(cobol) 01 dec pic 9(5)v9(2) comp-3. is equal to dec(7,2)

How to resolve SQL Code -310. The db table feild is declared as Decimal(7,2). Answer# 1 Check your program and make sure that the data you are passing to this host variable do not contain NON-DECIMALvalue.

I want to fetch the 10th row of a table which has been changed.How can I do it without going thru all the rows. Answer# 5 DECLARE cursor_name sensitivity SCROLL CURSOR FOR SELECT ... FROM ...

FETCH ABSOLUTE 10 FROM cursor_name

How to create a table using embedded sql? Answer# 1 JCL USED TO SUBMIT THE JOB:

//ITQCT1J1 JOB SIMOTIME,ACCOUNT,CLASS=1,MSGCLASS=0,NOTIFY=CSIP1//STEP01 EXEC PGM=IKJEFT01//SYSPRINT DD SYSOUT=*//SYSTSPRT DD SYSOUT=*//SYSOUT DD SYSOUT=*//SYSTSIN DD * DSN SYSTEM (DSN) RUN PROG (ITQCT1C1) END//

COBOL PROGRAM:

IDENTIFICATION DIVISION.PROGRAM-ID. ITQCT1C1.AUTHOR. SIMOTIME ENTERPRISES. ENVIRONMENT DIVISION. INPUT-OUTPUT SECTION. FILE-CONTROL.

***************************************************************** DATA DIVISION. FILE SECTION.

WORKING-STORAGE SECTION. ***************************************************************** * Data-structure for Title and Copyright... * ------------------------------------------------------------ 01 SIM-TITLE. 05 T1 pic X(11) value '* ITQCT1C1 '. 05 T2 pic X(34) value 'Create a Table and an Index '. 05 T3 pic X(10) value ' v04.12.01'. 05 T4 pic X(24) value ' http://www.simotime.com'. 01 SIM-COPYRIGHT. 05 C1 pic X(11) value '* ITQCT1C1 '. 05 C2 pic X(20) value 'Copyright 1987-2006 '. 05 C3 pic X(28) value ' SimoTime Enterprises, LLC '. 05 C4 pic X(20) value ' All Rights Reserved'.

01 SIM-THANKS-01. 05 C1 pic X(11) value '* ITQCT1C1 '. 05 C2 pic X(32) value 'Thank you for using this sample '.

05 C3 pic X(32) value 'by SimoTime Enterprises, LLC '. 05 C4 pic X(04) value ' '.

01 SIM-THANKS-02. 05 C1 pic X(11) value '* ITQCT1C1 '. 05 C2 pic X(32) value 'Please send comments or suggesti'. 05 C3 pic X(32) value 'ons to helpdesk@simotime.com '. 05 C4 pic X(04) value ' '.

***************************************************************** * Buffer used for posting messages to the console. * ------------------------------------------------------------ 01 MESSAGE-BUFFER. 05 MESSAGE-HEADER pic X(11) value '* ITQCT1C1 '. 05 MESSAGE-TEXT pic X(68).

EXEC SQL INCLUDE SQLCA END-EXEC.

***************************************************************** PROCEDURE DIVISION.

perform Z-POST-COPYRIGHT

perform ITEMRDB1-CREATE-TABLE

perform ITEMRDB1-CREATE-INDEX

perform Z-THANK-YOU.

GOBACK.

***************************************************************** ITEMRDB1-CREATE-TABLE. EXEC SQL CREATE TABLE ITEMRDB1 ( ITQ1_NUMBER CHAR (12) NOT NULL PRIMARY KEY, ITQ1_DESCRIPTION CHAR (48) NOT NULL, ITQ1_QTY_ONHAND INTEGER NOT NULL

WITH DEFAULT, ITQ1_QTY_ALLOCATED INTEGER NOT NULL WITH DEFAULT, ITQ1_UNIT_MEASURE CHAR (16) NOT NULL WITH DEFAULT, ITQ1_COST DECIMAL (9, 2) NOT NULL WITH DEFAULT, ITQ1_PRICE DECIMAL (9, 2) NOT NULL WITH DEFAULT, ITQ1_LADATE CHAR (8) NOT NULL WITH DEFAULT, ITQ1_LATIME CHAR (8) NOT NULL WITH DEFAULT, ITQ1_TOKEN CHAR (8) NOT NULL WITH DEFAULT, ITQ1_D_CODE_1 CHAR (1) NOT NULL WITH DEFAULT, ITQ1_D_PERCENT_1 CHAR (7) NOT NULL WITH DEFAULT ) END-EXEC

if SQLCODE = 0 display 'Finished CREATE of ITEMRDB1 ' else display 'ABENDING CREATE of ITEMRDB1 ' 'SQLCODE = ' SQLCODE add 16 to ZERO giving RETURN-CODE end-if exit.

***************************************************************** ITEMRDB1-CREATE-INDEX. EXEC SQL CREATE UNIQUE INDEX IDX1RDB1 ON ITEMRDB1(ITQ1_NUMBER ASC)

END-EXEC

if SQLCODE = 0 display 'Finished CREATE INDEX for ITEMRDB1 ' else display 'ABENDING CREATE INDEX for ITEMRDB1 ' 'SQLCODE = ' SQLCODE add 16 to ZERO giving RETURN-CODE end-if exit.

***************************************************************** * The following Z-Routines perform administrative tasks * * for this program. * ***************************************************************** Z-POST-COPYRIGHT. display SIM-TITLE upon console display SIM-COPYRIGHT upon console exit.

***************************************************************** Z-POST-MESSAGE. display MESSAGE-BUFFER upon console move SPACES to MESSAGE-TEXT exit.

***************************************************************** Z-THANK-YOU. display SIM-THANKS-01 upon console display SIM-THANKS-02 upon console exit.

insert into tablename a column1, column2 select column1, column2 from tablename b i am getting abend -104..can any one help? Answer# 2 insert into tablename(column1,column2)select column1,column2 from tablename

Alias names 'a' and 'b' should not be used.In case u want to use alias names, specify the column names with prefix as a.column1,b.column2 like that.Also, brackets must be given in an insert statement.

There is a 5000 EMP in company.how we find out the 5th highest getting salary employee outof 5000 employes........ Answer# 1 select max(sal) from emp a where 5=(select count(distinct sal) from emp b where a.sal<=b.sal);

There is a 5000 EMP in company.how we find out the 5th highest getting salary employee outof 5000 employes........ Answer# 2 select * from emp A where 4 = (select count(*) from emp B where A.sal<B.sal)

 

 In cursor program-1 can we create another cursor program-2 inside the cursor program-1.If yes how/no why ? Answer# 1 yes it can possible. Multibull cursor we can used.

 

I HAVE 2 TABLES ONE IS EMP_TABLE AND ANOTHER DEPT_TABLE.EMP_TABLE CONTAINS NAME,DEPTNO,DEPTNAME,LOCATION AND DEPT_TABLE CONTAINS DEPTNO,DOJ(DATE OF JOINING).I WANT TO DISPLAY NAME,DEPTNAME,DOJ AND WHO ARE JOINED BETWEN 01- JAN-2007 TO 01-JAN-2008? Answer# 2 Select...........from............where DEPT_TABLE.DEPNO = EMP_TABLE.DEPNO.AND DOJ between '01/01/2007' and '01/01/2008'

SELECT A.NAME ,A.DEPTNAME ,B.DOJFROM EMP_TABLE A ,DEPT_TABLE.EMP_TABLE B WHERE A.DEPTNO = B.DEPTNO AND B.DOJ BETWEEN '01-01-2007' AND '01-01-2008';

what needs to be done if a table is in copy pending / check pending status? Answer# 2 use repair utilityREPAIR SET TABLESPACE DATABASE.TABLESPACENAME NOCOPYPEND REPAIR SET TABLESPACE DATABASE.TABLESPACENAME NORCVRPEND REPAIR SET TABLESPACE DATABASE.TABLESPACENAME NOCHECKPEND

 

My DB2 program first read the data from a file and then it look into a table with the data it got from the file.If we did not bind the program , should the file read before SQL execution be success?? Answer# 2 Program successfully reads the file as the Bind is only for DB2 queries and it would give an abend(-805) once it reaches the SQL statement.

why should we bind the DB2 program . What if we did not BIND a BD2 program ?? Answer# 4 ACTUALLY BIND PLAN CONSISTS OF DBRM SO IN DB2 WE NEED TO CREATE UNIQUE PLAN NAME TO GET EXECTABLE LOAD MODULE AND DURING RUN PROG WE NEED TO GIVE PLANNAME AND MODIFIED SOURCE LIB .

IF WE DIDNOT BIND WE WILL GET -805 ERROR I,E PROG NAME OR DBRM NOT FOUND IN PLAN

SUPPOSE IF YOU MADE ANY CHANGES IN ANY PROGRAM THEN COMPILE AND BIND PACKAGE ,BIND PLAN .SUPPOSE IF U DONE EVERYTING AND FORGOT TO BIND IN THAT CASE YOU WILL GET -818 ERROR I,E TIMESTAMP ERROR...

what is a plan and how do you refer the plan in the program? Answer# 1 plan is executable.it contains access path.we will refer plan in jcl as plan name.generally all information abt the plan will be there in sysibm.sysplan

 

How do you Load a table ?? and what is Load replace ? Answer# 1 load a Table using LOAD UTILITY provided by db2.LOAD REPLACEreplace old data in the Table(resets a tablespace and related indexes to empty before loading)

 

How do you Load a table ?? and what is Load replace ? Answer# 2 In General, system will have standard PROC(DSNUPROC) to load the Table.Three symbolic parameter needs to be passed.But only one parameter is mandatory SYSTEM=DB2T(subsystem).Let me explain the Proc. The Proc contains a step with DSNUTILB as program to load the table. In Parms, pass the DB2 susbsystem. In SYSREC ddname points to the dataset that

contains the records to be load.SYSIN ddname points to the control card that is used to load the table.Control card contains information like tablename to be loaded, Position of the column and other options like LOAD with REPLACE or Load with RESUME, ENFORCE YES OR NO and LOG Yes OR NO.

When you want to specify REPLACE option, all the records or rows in the table are deleted before loading and RESUME option will retain the old records or rows in the table.

The tablespace will put in COPY PENDING status when you load the table with Option LOG NO. //DSNUPROC EXEC PGM=DSNUTILB,REGION=6144K,PARM='DB2' //SYSREC DD DSN=&LOADdataset,DISP=OLD//SYSIN DD DSN=&Controldataset,DISP=OLD

what are the frequent DB2 abends did you encounter in your programs ?? What are different SQL abends ?? Answer# 3 -100 when the rows found.-805 plan not found-305 null indicator not declared-104 invalid keyword-199 illegal use of keyword-206 table not foung-310 for decimal column using non decimal-407 try to insert the null value in the not null column-501 try to close the curosr which are already open-811 multirow select-504 cursor not decalred-818 time stamp mismatch-180 date error-181 date and time are invalid-414 in the union the column are not same-911 -913 both are for the deadlock-904 for the resource not avable-922 authorization failure-924 coneection is not availbke

I have a DB2 table which has 1000 rows.after udatingg first 110 rows, my job abends. Now what I have to do if I want to restart the job next time so that it should start updating from 111th row (without updating first 110 rows again). Answer# 1 Instead of commiting at the last point, commit at certain intervals (increase the frequency of commit, say after every 100 records in this situation)

Create one temporary table with a dummy record and insert

one record into the table for every commit with key and occurance of commit. And this insertion should happen just before the issue of commit.

And in the procedure division, first paragraph should read the last record of the table and skip the records that are already processed and committed. After processing all the records delete the entries in the table and issue one final commit.

 

How to solved 818 error Answer# 1 >> -818 time stamp mismatch.>> This is generated when the consistency tokens in the DBRM and the load module are different.>> So rebind the Cobol+Db2 program then successfully we run the program.

 

Do we have any optinon to search part of integer in DB2 as we have for character Like,SUBSTR optins? Answer# 1 DIGITS will convert INTEGER to character string

SUBSTR(DIGITS(PARTID),5,1) ='9' This will extract the 5th character and checks to see if it is 9.

Can we install 2 different versions of Db2 on same mainframe? Answer# 1 Yes you can. There could be multiple subsystems on a mainframe machine and its not necessary to have the same version of DB2 on all the Sub systems. One can have 7 and the other can have 8.1 etc...

 

Is it possible to precompile if db2 goes down? Answer# 1 Yes. Because the precompiler does not refer to the DB2 catalogue tables.

How to resolve the -305 error code in DB2? And also please let me know, how to resolve the db2 error codes. Answer# 1 supose, one of ur DB2 table column is defined as NOT NULL, if u trying to insert null value on that column at that time , u will get this error.

u can slove this error by handling the null indicator in ur program.

define null indiactor workign stroage variable with s9(4) comp.

How to resolve the -305 error code in DB2? And also please let me know, how to resolve the db2 error codes. Answer# 2 Before going to the solution, here is the explanation of a null indicator.

In DB2, a NULL is stored using a special one-byte null indicator which is attached to every NULLABLE column. If the column is defined as NULL, then the indicator field is used to record this. The indicator variable is transparent to an end user, but must be provided for when programming in a host language.

A positive value or a value of 0 means the column is not null and any actual value stored in the column is valid. A negative value indicates that the column is set to null. If the value is -2 then the column was set to null as the result of a data conversion error. The default is null.

There are two reasons for getting -305.

1) As said in the first answer if the table column is defined as NOT NULL (with no default) and if we try to insert a null value we get that. - This should be resolved by making sure the inserted value is not null. Null indicator cannot be used here since the column is defined as NOT NULL.

2) A table column is defined as NULL: The host variable has a not null value. The Null indicator is not set in the host program, so the null indicator is defaulted to a negative value. - This should be resolved by using a null indicator in the host program and moving the relevant value to the null indicator.

which SQL comment successfully removes uncommitted changes from a DB2 databaseA) rollbackB) deleteC) dropD) decommit

which authority can be granted to group of users using the GRANT stmt?A) SYSCTRLB) SYSADMC) DBCTRL

D) DB ADM

SYSCTRL and SYADM can be granted only to groups while DBCTRL and DBADM can be granted to both users and groups.

 what is contained in the DB2 node lock file?A) data base names B) data base users namesC) license keys

D) server names

Sorry, the right answer is C) license keys.

[1]pwd/var/ifor[2]more nodelock#5fbee0ee6feb.02.09.15.0f.48.00.00.00 qifx7bndg9b48n7gafpsa "" "8.1"#[admin_comment] "IBM Toronto Lab" "DB2 Enterprise Server Ed (Cust)" "2145852000" "0" "1" ""

 

how does the defining of a stored procedure effect the size of a DB2 data base A) it increases the size of a data base B) it decreases the size of a data base C) it does not effect the size of the data base D) it changes the table space structures Answer# 1 a - it increases the size of a data base.Because a stored proc is called from a program and thestored proc is stored along with the DB..hence the size ofthe db increases.

What is FREEPAGE and PCTFREE in TABLESPACE creation? Answer# 1 PCTFREE indicates to DB2 what percentage of each page in the table space or index to leave free when either loading or reorganizing the data. DB2 will then use that free space when inserting new rows and index entries.

FREEPAGE indicates to DB2 how often to leave a full page of free space when either loading or reorganizing the data. For example, if you specify FREEPAGE 5, DB2 will allocate a page of free space after filling five pages with data.

 

What does the REORG Utility do? Answer# 1 REORG is a db2 utlity, to reclustering, reclaim the table space. it used when the bulk updates or inserts happens.

 

What do you mean by NOT NULL WITH DEFAULT? When will you use it? Answer# 2 NOT NULL WITH DEFAULT indicates that the column cannot contain a NULL, if a user doesnt enter a value, DB2 generates the default.

What is the difference between TYPE 1 index & TYPE 2 index Answer# 1 TYPE 1 index has subpages and uses index locking. TYPE 2 index has no subpages and does data-only locking.

 

Name some fields from SQLCA. Answer# 1 SQLCODE, SQLSTATE, SQLERRD(3)

 

Can we declare DB2 HOST variable in COBOL COPY book? Answer# 1 No, We cant declare it in Cobol copy book, why because, Copy book is not expand during the precompilation, But if you declare it in DCLGEN, it will be expanded during precompilation.

CHECK THE ANS

The only place of VSAM KSDS in DB2 is? Answer# 2 The only place of VSAM KSDS in DB2 is in Boot Strap Data Set.

What is the difference between IN subselects and EXISTS subselect? Answer# 1 IN subselect will return rows that match the values in the IN list. EXISTS will return rows if the subselect return a true value.

What information can you find in SYSIBM.SYSLINKS table? Answer# 1 SYSIBM.SYSLINKS contains information about the table-to-table links that make up the physical structure and storage of the DB2 Catalog. Links are internal structures similar to RI relationships. Not all catalog tables use links.

Tablespace DSNDB06.SYSDBASE Indexes None Links DSNDR#DL REFERENCES SYSIBM.SYSRELS

What is the difference between SYNONYM and ALIAS? Answer# 3 A table or view can be referred to in an SQL statement byits name, by an alias that has been defined for its name, orby a synonym that has been defined for its name. Thus,aliases and synonyms can be thought of as alternate namesfor tables and views.An alias can be defined at a local server and can refer to atable or view that is at the current server or a remoteserver. The alias name can be used wherever the table nameor view name can be used to refer to the table or view in anSQL statementThe differences between aliases and synonyms are as follows:

* SYSADM or SYSCTRL authority or the CREATE ALIASprivilege is required to define an alias. No authorizationis required to define a synonym. * An alias can be defined on the name of a table orview, including tables and views that are not at the currentserver. A synonym can only be defined on the name of a tableor view at the current server. * An alias can be defined on an undefined name. Asynonym can only be defined on the name of an existing tableor view. * Dropping a table or view has no effect on its aliases.But dropping a table or view does drop its synonyms. * An alias is a qualified name that can be used by anyauthorization ID. A synonym is an unqualified name that canonly be used by the authorization ID that created it. * An alias defined at one DB2 subsystem can be used atanother DB2 subsystem. A synonym can only be used at the DB2subsystem where it is defined. * When an alias is used, an error occurs if the namethat it designates is undefined or is the name of an aliasat the current server. (The alias can represent anotheralias at a different server, which can represent yet anotheralias at yet another server as long as eventually areferenced alias represents a table or view.) When a synonymis used, this error cannot occur.

 

COBOL

if a dataset is already created with fixed length but after that i want to change fixed length to variable length then how is it possible Answer# 2 we can do that using Jcl and passing FTOV in the sysin parameter

There is a variable with value 19446. Requirement is to convert it to 194.46. I tried it by doing divide by 100 and my receiving field data type is 9(03)v99. But the output is 194. I am not getting the decimal value. Could anyone pls let me know how to get

this done? Answer# 1 When COBOL does a divide it uses the format of the item which in this case is integer only that is why the fractional part is missing. You need to move the value 19446 to a 9(5)v99 item first and then do the divide using the 9(5)v99 item.

how many maximum no of variables can be declared in linkage section ?

What is the default print format in cobol? Answer# 1 Default USAGE clause is DISPLAY

 

wirte a pgm in using files in which we hav 10 ,20,30 40...100 records in inputfile and i want them to be send to outputfile in reverse order. PLZ HELP ME OUT .........THIS IS A RECENT QUESTION IN IGATE.. Answer# 2 However, if there are a fixed no. of records, as you have mentioned in the question - which may not always be the case with Production Files(They may contain millions of records), you may follow this approach.

1. Read all the records into a COBOL array(table) A(1,2,...10).PERFORM VARYING I FROM 1 BY 1 UNTIL I>10 READ INPUT-FILE MOVE INPUT-RECORD TO WS-RECORD(I)END-PERFORM.

2. Print the data from the COBOL Array into Output-file.PERFORM VARYING I FROM 10 BY -1 UNTIL I<1 WRITE WS-RECORD(I)END-PERFORM.

wirte a pgm in using files in which we hav 10 ,20,30 40...100 records in inputfile and i want them to be send to outputfile in reverse order. PLZ HELP ME OUT .........THIS IS A RECENT QUESTION IN IGATE.. Answer# 2 However, if there are a fixed no. of records, as you have mentioned in the question - which may not always be the case with Production Files(They may contain millions of records), you may follow this approach.

1. Read all the records into a COBOL array(table) A(1,2,...10).PERFORM VARYING I FROM 1 BY 1 UNTIL I>10 READ INPUT-FILE MOVE INPUT-RECORD TO WS-RECORD(I)END-PERFORM.

2. Print the data from the COBOL Array into Output-file.PERFORM VARYING I FROM 10 BY -1 UNTIL I<1 WRITE WS-RECORD(I)END-PERFORM.

Define REDEFINE clause and Is it possible to have the redefine clause anywhere in the working storage section for a data name? Answer# 1 REDEFINE clause allows the said area to be referred by morethan one data name with different sizes and pictures.Usage:level-number data-name1 REDEFINES data-name2Answer to the question 2:The REDEFINES clause must immediately follow data-name1Other Rules:1. The level number of data-name1 and 2 must be identical.2. date-name1 and 2 must be of same size.3. Multiple redefinitions are allowed.

i have a variable block which is used in my cobol program as input file having records of 4080 after compilation while runing the program im getiing file attribut mismatch and it is saying tht the record length of the file is 4084 can any one knw the answer how to reslove it ? Answer# 1 For Variable length file(VB) first 4 characters are used to store the length of each record. So if your record contains 4080 then actual length is 4080 + 4 = 4084.

So to resolve abend --- increase record length to 4084.

 

how can you identify wheather the program is using search or search all in the cobol program? Answer# 1 The identification will be depend on many ways

The following are some ways:

1.first thing is that we can see the keywords like SEARCH orSEARCH ALL

2.Second thing is that the array type(single dimension ormultidimension) that is followed bySEARCH(multidimension/single dimension) or SEARCH ALL(onlysingle dimension) keyword.

3.third thing is that what are the relational operators we

used (multiple relational operators can be used in SEARCHlike AND OR etc... but in case of SEARCH ALL Only =relational operator is allowed )

4.And the fourth one is that(only one when condition will beused in SEARCH ALL. multiple WHEN conditions can be used inSEARCH)

what is the coding difference between COBOL and CICS. Answer# 2 COBOL is programing language and cics is front end tool useto create online process

when COMP-3 is preferrable? Answer# 2 Also, if you need more than 18 decimal digits, you must use COMP-3 (or DISPLAY) as COMP variables cannot exceed 18 digits while COMP-3 can be up to 31 digits.

01 var1 pic x(10) 01 var2 redefines var1 pic 9(10). then in procedure division move 'abcde' to var1 then waht is the value of var1 and var2 Answer# 4 redefines will reuse the memory with irrespective of datetype var1 is abcde and var2 also abcde.

can we declare s9(9)v9(9) in cobol ? if yes how many bytes it will occupy ?(urgent plz answer it) Answer# 4 s9(9)v9(9)

Here v stands for assumed decimal point so it will occupyonly 18 bytes(9+9)...if instead of s9(9)v9(9) it is coded ass9(9).9(9) then it is going to occupy 19 bytes ((9+9)+1)the extra 1 byte for decimal point .........There is no wayof occupying 20 bytes storage.

 created cluster using IDCAMS ..that is empty ..when i write a program for read using Input ..wil it open the cluster or gives any error? Answer# 1 Open statement itself fails when u try to open an empty file.

How do u debug a S0C7 abend? (aswered till we get the field which caused that) After knowing the field which caused that how do u know the record which caused that if it is in production env? (dumb) Ok let us assume that we got to know that

100th record caused that and I wanted to skip only 100th record from the file and process from 101th. How to do that in JCL using SORT? (tried with STOPAFT but ended up dumb when he said smthing else is ther) Answer# 1 If you came to know which field is responsible for that. you can run your testjcl with output file DISP parameter as (NEW,CATLG,CATLG), by doing so, your output file will get created irrespective of SOC7 abend becuase the last record of your outuput file will be responsible for generating abend.

Now you can strip off that record (because its bad data) and run your jcl.

How to Skip any record through JCL - You can use ICETOOL

//S1 EXEC PGM=ICETOOL //TOOLMSG DD SYSOUT=* //DFSMSG DD SYSOUT=* //IN DD DSN=... input file //OUT DD DSN=... output file //TOOLIN DD * SUBSET FROM(IN) TO(OUT) REMOVE INPUT RRN(100)

this will remove 100th record from your file and will copy rest all records into ouptput file.

 

My interviewer ask A calls B and C calls B, a and b are static c and b are dynamic.what happens if they compile and execute at same time. Answer# 1 in static call we cannot comile the main program without compiling the subprg(B), you can get the error that is object not found.

in dynamic call we can compile the main program without compilation of subprg(B), but while executing the prg then u can get the abend i.e, load not found that means s806.

 

Most of the modern compilers support upto 31 digits. Try compiling your program and you'll be able to find out.

Else, if your requirement is only to store and not do any computation, you can have a group variable.

05 My-number. 10 My-number-part1 pic 9(15). 10 My-number-part2 pic 9(5).

 

z(2) occupies two bytes and will supress the leading zeros

z9(2) occupies 3 bytes and will supress the leading zeros in the first byte.

where do u use low-value and high value in cobol Answer# 1 When you need a data field to have value less than any other data INITIALZE or MOVE LOW-VALUES to that filed. same for HIGH-VALUES when you need that field to have valuue more that any other data.

Suppose, file A has 100 records and file B has 500 records. We want to write records common to both A and B into file C and records which are present only in either A or B into another file D. What should be the logic of Cobol program to achieve this? Answer# 5 correction to #3sort files Asc

if f1 = f2move file1-rec to file3-rec. write file3-recread f1 & f2.if f1 < f2move file1-rec to file4-rec. write file4-rec.read f1.if f1 > f2move file2-rec to file4-rec. write file4-recread f2.

WE HAVE 2 FILES IN COBOL. ONE IS FIXED LENGTH RECORDS ANOTHER ONE IS VARIABLE LENGTH. IF I DECLEAR LRECL OF FIXED ONE AS 80 AND 2ND RECORD AS 132. WHAT WE NEED TO DECLEAR LRECL FOR THOSE 2 FILES IN JCL? Answer# 3 Narayan - A small correction LRECL for fixed - 80 and LRECL for Variable block records is 132+4 = 136 .

u have passed sme charecters thru parm parameter in jcl. how do u code in cobol to recieve the values u gave in parm ? Answer# 1 by using 'USING' keyword in procedure division and dataname.one restriction is, declaring length variable and datanamein linkage section of covol program.

 

is it mandatory to give data division before procedure division ? wht happens if i give procedure division first thn data division ? reply soon Answer# 1

THE COMPILATION JOB GETS ABENDED WITH MAXCC=12.