Mainframe For Dummies

Mainframe - WORK SHOP on Project Case Study

2012-01-09T10:25:00.000-08:00

Mainframe - WORK SHOP on Project Case Study

· Work shop is executed at its own standards(mainframes).

· Work shop runs for 15 Days (2hrs of Theory + lab unlimited)

· Follows Water Fall SDLC

o Impact Analysis

o Estimation

o Design

o Coding

o Testing

· Technical Approach/s to solve problems/abends

· Refreshing Important Technical Concepts

· Usage of various tools

Write to mfdtech@gmail.com for day-day schedule/ for Demo.

Class Room Session available @ Bangalore / India Only

For E-Class pls write to above mail id.

Match_merge_logic

2010-06-19T08:41:00.001-07:00

MATCH - MERGE LOGIC

Match Merge logic is extensively used in COBOL programs to compare the data in two sequential files effectively. Only pre-requisite to for using this logic is, all input files to be in Sorted Order

Problem : I have 2 Sequential files File1 and File2.

If Record exists in both the files then write data into FILEA

If Record exists only in File1 and not in File2 then write data into FILEB

If Record exists only in File2 and not in File1 then write data into FILEC

Solution: The effective way of solving above problem is, by using Match Merge Logic. Only pre-requisite to for using this logic is, all input files to be in Sorted Order. First step sort I/P file

Explanation of Logic : Below listed are I/P and Output files data for say RollNo

File1 File2 FILEA FILEB FILEC

10 10 10

20 20 20

30 50 30

50 60 50 60

70 99 70

90 90 99

Open FILE1 and 2 in input mode, FileA, B, C in output mode

1. Read File1 (Reads the record 10 )

2. Read File2 (Reads the record 10 )

3. Compare the Key Record of both Files (File1-Rno = File 2-Rno).

a. 10 = 10; means record exists in both files)

b. Write the record in FILE A

c. As we need to advance further, repeat Step1 thru Step3

4. When system reads the records 30 and 50, Step3 will Fail. So evaluate Step4

a. If File1-Rno < File 2-Rno (30 < 50)

b. Record 30, which is > 20 and <50, means Rollno 30 is not present in FILE 2

c. i.e., Record 30 exists only in File1

d. Write the record into FILE B

e. Now Read only FILE 1, and repeat thru step 3

5. When system reads the records 70 and 60 from file1 and 2 respectively, Step3,4 will Fail. So evaluate Step5

a. If File1-Rno > File 2-Rno (70 > 50)

b. Record 60 of file2, which is > 50 and <70, means Roll no 60 is not present in FILE 1

c. i.e., Record 60 exists only in File2

d. Write the record into FILE C

e. Now Read only FILE 2, and repeat thru step 3

6. Repeat above steps until end of both the files.

Close all files

Advantages : Open/close both file only once.

Faster

Inter_View_Qns2

2010-06-19T08:40:00.001-07:00

1. Where do we use TSQs

Data in TSQ is stored as VSAM files

a. Pageup and Pagedown Logic

b. For writing Log Details (as we cant use Displays in CICS)

2. How do we access the TSQ using a Key field

a. Using RIDFIELD

3. What is Sync Point and why is called as 2 Phase Commit

a. Sync point is issued to save the data till that LUW.

b. This is called as 2 Phase commit as , first it saves the data pertaining to File, Db2 then CICS data.

4. I would like to generate only Input field (along with A, F, L) and not Output files in my Symbolic Map, how to achieve this ?

a. Use Mode = IN , in BMS macro

5. How do you position your cursor on Map.

a. Code IC, in Attribute field of BMS macro of respective field

b. Move -1 to Length field of respective field

c. Using SEND MAP CURSOR ( 100 )

6. How do you test Physical Map layout in CICS region.

a. Use CECI send Map () Mapset()

7. What’s the debugging tool used for CICS

a. We use CEDF/Intertest/Expeditor.

8. How do you test / check the Cobol flow thru CEDF

a. Use the Option TRACE

9. How do Read a Vsam file from Bottom to Top

a. Move HIGH-VALUES to WS-KEY and read previous

10. How do you execute Even steps in a job. Eg . execute only step2 in below example

//job1 job ACC, Class=a,

//step1

//step2

//step3

We can use the utility IEBEDIT or Use COND parameter. To skip first step use COND=ONLY, (this will flush the step1)

DB2-IMS - Few Difference

2010-04-28T04:10:00.001-07:00

	Cobol-DB2	Cobol -IMS

1	Need to Prcompile to generate 1. DBRM (Sql Staments only) 2. Cobol Object code	No Precompilation Required
2	Bind the DBRM to Generate Plan. Plan consists of Executable SQL Quries	No Bind Process is Required. Here we Write PSBs to fetch respective Record.
3	Compile Cobol Code	Compile Cobol-Ims program
4	Execute Cobol - DB2 Program using IKJEFT01, where in we Specify Program Name and Plan Name to be Executed	Execute Cobol-IMS program using DFSRRC00. Pass Program Name , PSB, Name, Plan name Thru Parm Parameter

	Why We need BIND In DB2 not in IMS

	Say below query is written in Cobol-Db2 Program	Equivalent IMS Call

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

How to Solve SOC7 Abend - with screen shots

2009-08-30T10:56:00.001-07:00

Below process helps to find out the statement, caused the SOC7 error.

Check the Sysout of RUNJCL. This shows the error statement and lists offset value

Take the Offset Value 000003C0

Got to respective Compilation Job listing, check the sysprint

Search for the offset value 0003C0 (delete +00 -- initial 3 letters of Offset value and search for it) check below 2 screen shots

This Offset value is listed under line no 0045 – which refers to Move statement.

Take this no. 045 and find for it in same sysprint. This points to the exact statement, caused SOC7

This 045 pints to the Move statement 1526, this is the exact line in the program

Check for the above line no. In source program. This points to the statement highlighted below.
Check the statement, variable check-4, which is added to check-6. These are having different Picture clause.
check-4 is alfhanumaric, holding some junk data, when this data is moved to Chcek-6 variable(of comp-3) creates SOC7 error.
This is just an example to explain one cause for Soc7 error and how to find the rootcause statement.

Method - 2 .

If abend-aid is available, check the same.

This will directly provides the error statement and line no.

steps in CICS-DB2 prog ins. screen shots

2009-05-05T11:28:00.001-07:00

DB2-CICS Program Compilation and Execution Process

1) Write a DB2/CICS program and compile it using DB2/CICS compailer JCL

2) Use BIND JCL for binding process

3) Now Sign on to CICS Region.

4) Define and install the Map .

5) Define and Install the Program.

6) Check whether there is any DB2 Connection. If not Create a connection using

ceda def db2c(....)

7) Define and install the DB2 Entry using

ceda def db2e(.......)

8) Define and install the DB2 Transation using

ceda def db2t(.......)

9) Define and install the Transation

10) now your setup is ready and you can run your program using the transation ID

Sample Screen shots – used for defining above process

4a. Define the map : MYMAPS

4b. Install map : MYMAPS

5a. Define the Program : PGM

5b. Install the Program : PGM

7b. Define the DB2ENTRY : VINAY

7b. INSTALL the DB2ENTRY : VINAY

8.a. DEFINE the DB2ENTRY : HARI

8.a. INSTALL the DB2ENTRY : VINAY

GROUP = VIJAY

9.a. DEFINE the transaction for the program(Pgm) : VBVB

9.b. Install the transaction for the program(Pgm) : VBVB

NOTE :

Before running this program check whether DB2 connection is defined using :

ceda def db2c(....) .. if no connection define it .

Why DB2 and why not VSAM (DB2 Vs VSAM)?

2009-04-27T05:47:00.000-07:00

Why DB2 and why not VSAM (DB2 Vs VSAM)?

This is the common query raised by the programmers, whether to go with DB2/VSAM, hope below information helps.

	VSAM	DB2
Definition	Database with file Concepts	Database built on File management storage system-DBMS concepts

Security levels	Provides at Dataset level only	Provides Good security levels at various levels (like table, table space etc.,)

Data Maintenance	It’s difficult compared to DB2. Data retrieval or insertion or updates etc, is a bit difficult (puts more burden on programmer to achieve this). Simply speaking Query interface is not supported	It’s easy to maintain data by using SQL Data retrieval or insertion or updates etc, is far easier using SQl . Query interface is supported

Referential Integrity	Again it programmers responsibility to design it accordingly	It’s one of the feature of DB2

Recovery	Recoverable only from Backup file Auto recovery is not possible	Recoverable from DB2 log/ Backup Auto recovery is possible

Backup	Incremental backup is not supported.	Incremental backup is supported

Performance	Performance is better than DB2, when small amount of data is used by the transaction	DB2 has good performance over VSAM, when high volume of data is used by the transactions

Accessible	Mainframes only	Can be shared with Distributed systems

DB2-CICS Program Compilation and Run Process

2009-03-16T00:44:00.000-07:00

Write a DB2/CICS program
Compile it using DB2/CICS compailer JCL
Use BIND JCL for binding process
Now Sign on to CICS Region.
Define and install the program.
Define and install the Transation.
Check whether there is any DB2 Connection. If not Create a connection using ceda def db2c(....)
Define and install the DB2 Entry using ceda def db2e(.......)
Define and install the DB2 Transation using ceda def db2t(.......)
now your setup is ready and you can run your program using the transation ID

2008-07-14T04:20:00.000-07:00

Future for Mainframes in SOA

While it is far too early to get an answer it is now possible to ask, as a serious rather than trivial question, whether there is scope to see new sales of zSeries mainframes grow into the SOA and web services-based market sectors as a result of new developments in integration tools, such as the recent appearance of DataDirect Technologies' major upgrade of its Shadow integration platform, for the traditionally back-office machines. Will it be enough to prove to be a positive lever on green field zSeries sales?

It might still be a brave person who answered ‘yes’ right now, the opportunity now exists where the mainframe can start to play a full part in a web-services/SOA environment, running applications that can proactively participate in that environment rather than just be a passive contributor to it. While it may not be natural to think of a mainframe as a core system in such an environment, Version 7 of Shadow does offer the potential to move IBM's zSeries systems well beyond the traditional role of back-end, back-office batch system.

Given that Java is a strategic part of the Shadow V7 enhancements the inherent I/O capabilities of the machine open up some interesting new possibilities in terms of managing high density real-time business applications such as online transactions in, say, retail or financial environments. In fact a wide range of Java applications can now exploit the high-capacity I/O capabilities of the mainframe. This could mean that the investment made in existing mainframe systems can now produce an almost infinite return. It can also make the notion of new mainframe investments for web-services and SOA-based applications and infrastructures quite apposite.

The addition of integration tools for widely used web service-related standards such as SOAP and XML will now make it possible for mainframe business logic and web services to communicate directly, allowing the mainframe to start to play a front-line role in providing important business process services to underpin web-service delivery in real time.

There are two useful tricks performed by DataDirect with Shadow. One is its abaility to integrate data directly with the common schema of web services, such as SOAP and XML, as well as the widely used SQL interface. This takes the mainframe to a new and much richer level of direct integration with the web services and SOA world. The second is Shadow's exploitation of the IBM zIIP and zAAP processors. Their arrival has already had an impact, allowing customers to run new and expanded workloads and, in particular, DB2 database workloads, on the mainframe in a cost-effective manner. The specific advantage here is access to a high speed processor engine that runs with none of the normal performance/pricing issues associated with the system in use. These engines are available to users for a one-off price rather than being geared to the mainframe processing capacity (MSU) pricing restrictions associated with the mainframe General Purpose Processor, yet are allowed to run at maximum speed.

Access to these processing engines is possible using either Task Control Block (TCB) or Service Request Block (SRB) threads. By developing patented technology that allows applications using TCB or SRB threads on the zIIP and zAAP processors, DataDirect has not only provided a significant performance improvement for existing applications but also opened zSeries machines up to some significant new operational possibilities. For example, expansion of the data integration tools to cover ODBC, JDBC and ADO means that new mainframe data queries to IMS, VSAM, IDMS and Adabas are now possible. The product will feature DataDirect's line of client drivers, which support ODBC 3.5.2 for 32-bit and 64-bit computing, JDBC 3.0 Type 4 for 32-bit and 64-bit database drivers and ADO.NET data providers. The SOAP and XML integration tools will allow parsing between mainframe business logic and web-services screen logic.

Many of these drivers are already being widely used by ISVs, which provides the added advantage of opening up the mainframe as a target platform for their applications offerings, widening both their market potential and the range of applications options available to existing zSeries users.

The key here is that, by allowing the IBM Java Virtual Machine (JVM) to run within the Shadow address space, it allows Java applications to run, at maximum system performance and no extra running costs, on the zIIP and zAAP engines. This way one of the problem areas of running Java applications on a zSeries machine—the operational expense—can be overcome, making the zSeries a viable option for running Java applications. Given the natural propensity towards applications with a high I/O demand, this should make zSeries machines an interesting new platform option for many typical java-based business applications.

For more info refer to URL : http://www.it-director.com/enterprise/content.php?cid=10009

Impact Analysis

2008-06-04T08:04:00.000-07:00

Impact Analysis :

I hope Below informaiton will provid you brief information on various steps involved in impact analysis ( when ur asked to add a new field?)

Impact Analysis : This is a process to identify the various Copy books or Programs of Jcls or DB2 table etc are to be modified/impacted due the new changes , to meet the requirement.As per the requirement, programmer will have an idea, like what are the New fields/Existing field to be added/modify in

1. Copy Books

2. Files/Jcl

3. Database

4. In online Screen etc.

1. Impact Analsys On Copy Books (used by cobol programs) :

Identify the copybooks to be modified as part of requirement in list1
Now search for various copy books Impacted because of the copy books listed in list1 and note them into new list List2
Now search for various copy books Impacted because of the copy books listed in list2 and note them into new list List3
Above process is continued till the search returns zero copy books impacted.
Merge all above lists and eliminate the duplicate copy books from them.
Now find out the various programes which are using the above list of copy books and list them in Excel Sheet

2. Impact Analsys On Files/JCL :

Find out the Varous JCL/Procs impacted due to change in File length.
Find Out Varous JCLs/Procs impacted due to Sort cards/ Datalibs impacted due to above change.

3. Impact Analsys On DB2 tables :

Find out all the tables impacted due to addition of new table fields
Find out the load cards impacted due to these changes

4. Impact Analsys On CICS screens :

Identify various screens/screen programs impacted due to addition of new fieldsTip : Use 3.13 option on mainframes to search a field in specfic lib.

walkinn for freshers

2008-06-04T01:56:00.000-07:00

walkin for Freshers -- TCS HYD

Students who have just completed their graduation/post graduation can apply for trainee positions.) for technical writr position . Eligible candidates can walk in for a written test and interview at TCS, Deccan Park on Saturday, 07 June 2008, at 10:00 am.

.........-----------------------------------------------------
-----------------------------------------------------------------
Req for DELL
Hi all please forward your 2007 & 2008 passed out resumes to belowmentioned emails.Dell is planning to recruit more than 1000 new graduates for their new webmail development, in various locations. This is Great Opportunity for FreshGraduates to work with DellQualification:ØBE / B. Tech. - 2008 pass outs (Computer Science, Information Technology,Electronics and all Electronics, Electronics & Telecommunication,Electronics & Instrumentation etc.) Ø MCA / M.Sc. -2007/08: Only ComputerScience & Information Technology Branch Ø B.Sc./BCA - 2007/08: ComputerScience, Information Technology, Physics, Mathematics, Chemistry,Statistics, ElectronicsEligibility Criteria:ØGraduation 60% and AboveØ 10th/12th: 60% and aboveØ No Gap in educationØ Willingness to work in shifts, any platform & any location Ø NoSupplementary (un-cleared) subjects as on date Ø Good Communication SkillsIf you have resumes meeting the above criteria you can send it to:
Bangalore: offcampus.bangalore @dellmail. com
Chennai: offcampus.chennai@ dellmail. com
Hyderabad : offcampus.hyderabad @dellmail. com

IBM DB2 UDB 701 Questions

2008-03-08T01:52:00.000-08:00

Exam Name: IBM DB2 UDB V8.1 Database Administration

Exam Code: 000-701

Question: 1

Given the following command

Create database payroll alias paynew on path/drive

Using codeset codesst

Territory territory

Collate using identity

How is character data compared within the database?

A. Byte for byte

B. Based on the codest

C. Based on the codepage

D. Based on the territory

Answer: A

Question: 2

Given the following statement

Drop table payroll. Employee"

Returns the following message.

Solo55in "user" does not have the priviege to perform operation

"drop" on object "pavroll. Employee." Sqlstate=42501

Which of the following will correct the situation?

A. GRANT DROP AUTHORITY TO user1

B. GRANT DROPIN ON SCHEMA user1 TO user1

C. GRANT DROPIN ON SCHEMA payroll TO user1

D. GRANT DROPIN ON SCHEMA employee TO user1

Answer: C

Question: 3

Which of the following is required to support infinite active log space?

A. USEREXIT = ON, LOGSECOND = 0

B. USEREXIT = ON, LOGSECOND = -1

C. LOGRETAIN = RECOVERY, LOGSECOND = 0

D. LOGRETAIN = RECOVERY, LOGSECOND = -1

Answer: B

Question: 4

Which of the following commands can be issued in a Command Line Processor (CLP) session to

capture explain information for subsequent SQL statements that can be formatted using db

2exfmt?

A. SET CURRENT SNAPSHOT YES

B. SET CURRENT QUERY EXPLAIN

C. SET CURRENT EXPLAIN MODE EXPLAIN

D. SET CURRENT EXPLAIN SNAPSHOT EXPLAIN

Answer: C

Question: 5

The status of jobs scheduled by the TaskCentercan be monitored using which of the following?

A. Journal

B. Health Center

C. Snapshot Monitor

D. Information Catalog Center

Answer: A

Question: 6

Which of the following authorities can be used to following through database logo.But NOT

restore a backup image into a new database?

A. DBADM

B. SYSADM

C. SYSCTRL

D. SYSMAINT

Answer: A

Question: 7

Giving the following information:

1. A full database backup of DB1 was taken at 1:00 PM

2. A table space TS1 was backed up at 1:15PM

3. A table in TS1 was dropped at 2:00 PM

Which two of the following commands are valid after restoring table space TS1 from the full

database backup taken at 1:00PM?

A. ROLLFORWARD DATABASE db1 TO 1:15PM

B. ROLLFORWARD DATABASE db1 TO 2:30PM

C. ROLLFORWARD TABLESPACE ts 1 TO 2:30PM

D. ROLLFORWARD DATABASE db1 TO END OF LOGS

E. ROLLFORWARD TABLESPACE ts 1 TO END OF LOGS

Answer: B, D

Question: 8

A table is experiencing frequent inserts. Which of the following options can defer how frequently

DB2 must allocate additional index pages?

A. CLUSTER

B. PCTFREE

C. MINPCTUSED

D. SPECIFICATION ONLY

Answer: B

Question: 9

Which of the following requires each row in a table to be unique?

A. A foreign key

B. A bidirectional index

C. An index with include columns

D. An index created using PCTFREE

Answer: C

Question: 10

Exhibit:

Which of the statements and in what order must they be called to prevent normal users from

seeing SALARIES, but allow them to see NAMES?

A. 1

B. 2

C. 1,4,3

D. 1,5,3

Answer: C

Question: 11

Which of the following is automatically granted to PUBLIC when a database is created?

A. LOAD

B. PASSTHRU

C. CREATETAB

D. CREATE_NOT_FENCED

Answer: C

Question: 12

How dose DB2 enforce table check constraints for data added to table with the LOAD utility?

A. With the BUILD phase of the LOAD

B. With the SET INTEGRITY statement

C. With the DELETE phase of the LOAD

D. With the UPDATE CONSTRAINTS statement

Answer: A

Question: 13

Giving the following DDL statements:

CREATE VIEW v1 as SELECT col1 FROM tab1 WHERE col1>10

CREATE VIEW v2 as SELECT col1 FROM v1 WITH CHECK OPTION

CREATE VIEW v3 as SELECT col1 FROM v2 WHERE col1<100

Which two of the following statements will fall?

A. INSERT INTO v1 VALUES (5)

B. INSERT INTO v2 VALUES (5)

C. INSERT INTO v3 VALUES (5)

D. INSERT INTO v3 VALUES (100)

E. INSERT INTO v3 VALUES (200)

Answer: A, B

Question: 14

Which of the following DB2 utilities can create a table and place data into the table?

A. LOAD

B. INSERT

C. IMPORT

D. UPLOAD

Answer: C

Question: 15

Which of the following is required to use the IMPORT utility to import data into a table?

A. SYSCTRL authority

B. LOAD authority on the table

C. ALTER privilege on the table

D. IMPORT authority on the table

E. INSERT privilege on the table

Answer: E

Question: 16

Which two of the following commands would give the minimum point in time of recovery for table

spaces in a recoverable database?

A. GET DBM CFG

B. LIST MINIMUM POINT IN TIME

C. LIST TABLESPACES SHOW DETAIL

D. GET DB CFG FOR <database_name>

E. GET SNAPSHOT FOR TABLESPACES ON <database_name>

Answer: C, E

Question: 17

Which of the following commands should be run immediately after reorganizing a table?

A. REORG

B. RUNSTATS

C. DB2RBIND

D. FLUSH PACKAGE CACHE

Answer: B

Which of the following privileges is granted to PUBLIC on the system catalog views when a

database is created?

A. NONE

B. SELECT

C. UPDATE

D. CONTROL

Answer: B

Question: 19

Exhibit:

How many rows will be returned after issuing the following SQL statement?

SELECT* FROM PERSON?

A. 1

B. 2

C. 3

D. 5

E. 10

Answer: C

Question: 20

There is an instance on a server that needs to be discovered. The two databases in the instance

are PAYROLL and CERTIFY. The PAYROLL database should not be seen. Which of the

following will meet this requirement?

A. Set the DAS configuration parameter DISCOVER to DISABLE.

Set the DISCOVER_DB parameter in the CERTIFY database configuration file to ENABLE.

B. Set the DAS configuration parameter DISCOVER to SEARCH.

Set the DISCOVER_DB parameter in the PAYROLL database configuration file to DISABLE.

C. Set the DAS configuration parameter DISCOVER to SEARCH.

Set the DISCOVER_INST parameter in the PAYROLL database configuration file to

DISABLE.

D. Set the DAS configuration parameter DISCOVER to DISABLE.

Set the DISCOVER_INST parameter in the CERTIFY database configuration file to ENABLE.

Answer: B

Also look @ http://exam.testinside.com/000-701.htm for few more demo Qns

Page 1 of 4

NULL VALUES and NULL INDICATORS in DB2

2008-02-25T03:42:00.000-08:00

In DB2, the columns defined as NULL needs to be handled carefully else it will throw null exception error, in order to over come this error data type can be handled by using null indicator.

NULL is stored using a special one-byte null indicator that is "attached" to every nullable column.
If the column is set to NULL, then the indicator field is used to record this.
Using NULL will never save space in a DB2 database design - in fact, it will always add an extra byte for every column that can be NULL. The byte is used whether or not the column is actually set to NULL. The indicator variable is transparent to an end user

Consider below Table :

Create Table SAMP_TAB

SN CHAR (10)

SNAME CHAR (10)

STATUS CHAR (2) NOT NULL BY DEFAULT

CITY CHAR (10) NOT NULL

Note :: Unless you specify NOT NULL, the default is to allow for NULL

In above table SN and SNAME columns holds null values by default, in order to handle these null variables we need to have NULL-INDICATORS declares in the Program as S9(4) comp variable (A indicator variable is shared by both the database manager and the host application. Therefore, this variable must be declared in the application as a host variable, which corresponds to the SQL data type SMALLINT)

Let us declare the Null indicators for above two variables in application program as

02 SNAME-INDNULL S9(4) comp

05 SN-IN S9(4) comp

What values Null indicators will hold :

‘-1’ : Field is having NULL value
‘ 0’ : Field is Not NULL value
‘-2’ : Field value is truncated

How /Why to handling Null Values:

When processing INSERT or UPDATE… statements, the database manager checks the null-indicator variable, if one exists. If the indicator variable is negative, the database manager sets the target column value to null, if nulls are allowed else it throws sql error code -305, we need null indicators to handle this situation.
If the null-indicator variable is zero or positive, the database manager uses the value of the associated host variable.

There are two reasons for getting -305 and Resolution :

1) If the table column is defined as NOT NULL (with no default) and if we try to insert a null value we get this error.

Resoulution :This should be resolved by making sure that the inserted value is not null. Null indicator cannot be used here since the column is defined as NOT NULL.

(validate the data, if its not numeric or less than spaces then move spaces into it and then insert or update into table)

2) A table column is defined as NULL, The host variable has a not null value and the Null indicator is not set in the host program, so the null indicator is defaulted to a negative value.

Resoulution :This should be resolved by using a null indicator in the host program and moving the relevant value to the null indicator. Here inorder to move null value into respective column nove -1 to null indicator.

Eg :

MOVE -1 to SNAME-INDNULL

EXEC SQL INSERT INTO SAMP_TAB

(SN,SNAME,STATUS,CITY) VALUE

(:SN,:SNAME:SNAME-INDNULL,:STATUS,:CITY)

END-EXEC

Eg :

EXEC SQL SELECT SNAME

INTO :SNAME:SNAME-INDNULL

FROM SAMP_TAB

WHERE SN = :SN-IN

END-EXEC

(Note : If SNAME has a value, SNAME-INDNULL contains 0.

If SNAME is NULL, SNAME-INDNULL contains -1. )

If SQL-CODE = -305 and SNAME-INDNULL = -1

Display “ SNAME is having null values “

Else

End-If

Important Points wrt NULL Variables

NULLs can present problems because they are handled differently by different computers and the collating sequence is inconsistent with regard to NULLs.
Unless you specify NOT NULL, the default is to allow for NULLs
It's easy for us to get lazy and allow columns to contain NULLs when it would be better to specify NOT NULL
Remember to allow for NULLs creating UNKNOWN logical values. Always test your code with NULLs in all possible places.
The NULL is a global creature, not belonging to any particular data type, but able to replace any of their values.
A NULL isn't a zero, it isn't a blank string, it isn't a string of length zero.
The basic rule for math with NULLs is that they propagate. An arithmetic operation with a NULL will return a NULL. If you have a NULL in an expression, the result will be NULL.
If you concatenate a zero length string to another string, that string stays the same. If you concatenate a NULL string to a string, the string becomes a NULL.
In comparisons, the results can be TRUE, FALSE, or UNKNOWN. A NULL in a row will give an UNKNOWN result in the comparison.
Sometimes negating the wording of the problem helps. Instead of saying "Give me the cars that met all the test criteria," say "Don't give me any car that failed one of the test criteria." It is often easier to find what you do not want than what you do want. This is very true when you use the NOT EXISTS, but beware of NULLs and empty tables when you try this.
You can't completely avoid NULLs in SQL. However, it is a good idea to try as hard as you can to avoid them whenever possible.
Make yourself think about whether you really need NULLs to exist in a column before you omit the NOT NULL clause on the column definition.
Use NULLs sparingly

JCL - MAXI -VALUES

2008-02-17T02:22:00.000-08:00

Question: HOW MUCH SPACE OS ALLOCATES WHEN YOU CREATE A PS OR PDS?
Answer: 56 KB

Question: MIN NO OF DATASET NAMES (PDS) IN ONE DIRECTORY BLOCK?
Answer: FOUR - SIX

Question: THE MAXIMUM NUMBER OF STEPS IN A JOB?
Answer: 255

Question: MAX. NO OF DD STATEMENTS IN A JOB
Answer: 3273

Question: What is the maximum number of instream procedures?
Answer: 15

Question: for how long a job can be executed continuously in a mainframe
Answer: 248 DAYS

Question: How much is memory space involved, when we code BLOCKSIZE,TRK & CYL
Answer: One block constitutes 32KB of formatted memory/ 42KB of Unformatted memory, 6 blocks makes one Track & 15 Tracks makes one cylinder.

Question: What is DSNDB06 ?
Answer: This is the Place where DB2 Catalog resides

Question: What is the use of DSNDB07 ?
Answer: This is the area where sorting takes place in DB2

Question: WHAT IS DATACOM DB?
Answer: IT IS A DATABASE USED WITH VSE.

Question: What are the maximum and minimum sizes of any CONTROL AREA (VSAM datasets) ?
Answer: Minimum Size : 1 trackMaximum size : 1 cylinder

Question: WHAT IS COMM?
Answer: COMM - HALF WORD BINARY

Question: What is a procedure?
Answer: A set of precoded JCL that can be modified through the use of parameters or override cards. Note: Procedures can be catalogued or instream.

Question: Q: HOW MANY TIMES SECONDARY SPACE ALLOCATED?
Answer: A: 122 TIMES

DB2 Basics - Quick Refernce

2008-02-16T23:25:00.000-08:00

Relational Database Management System(RDBMS)
Universal Database(UDB)

This is one of the sub systems in Mainframes.

Any number of Sub Systems can be created in Mainframes as per the requirements.

Hierarchy of DB2 Sub system:

TS – Table Space INS – Index Space

T – Table IND – Index

V – view

Maximum storage space for a Table Space is 64 million bytes.

SQL can be used to Create, Delete, and Update and Query the Objects

SQL queries can be executed by the following techniques

Application programming
Tools like QMF (Query Management Facility)

SPUFI (SQL Processor User File Input)

DATA TYPES :

Integer -- 4 bytes

Small int -- 2 bytes

Char(n) – N bytes

Varchar(n) – N+2 bytes

Graph(n) – 2n bytes

Vargraph(n)– 2N+2 bytes

Date – 10 bytes

Time – 8 bytes

Timestamp – 26 bytes

NORMALIZATION :

Arranging the data in the Database in organized manner.

1NF: Avoiding multiple values or set of values in one column.

2NF: Avoiding repeated rows by defining primary key.

3NF: Separating functionally dependent and non-functionally dependent columns

Primary key :-

Uniquely identified row
Which can be formed with single or multiple columns
Does not allow duplicate records
Cannot contain Null

Foreign key : -

Another identifier which can be used to build relationship between the tables
Must be the primary key of parent table with same data type & length
Can consists of single or multiple columns
Can contain Null or duplicate rows
Multiple foreign keys can be defined in one table
Foreign key should be defined at the time of defining child table in the create command by “WITH REFERENCES” option.

CREATE TABLE ITEM(

INO INTEGER,

INAME CHAR(15),

CNO INTEGER,

PRIMARY KEY IS INO,

FOREIGN KEY IS CNO

WITH REFERENCES CUST)

REFERENCE INTEGRITY:

The relationship between two tables which can be achieved by defining foreign key.

PRIMARY KEY	FOREIGN KEY
Cannot contain Null values or duplicate rows	Can contain Null values or duplicate rows
Cannot be updated	Can be updated
Can be defined as a foreign key in other table	which must be primary key of another table
only one primary key can be defined for one table	multiple foreign keys can be defined for one table

SQL(Structured Query Language)

DDL (Data Definition Language)

Create, alter, drop

DML (Data Manipulation Language)

Insert, update, select & delete

DCL (Data Control Language)

Grant, Revoke

TCL (Transaction Control Language)

Commit, Rollback

Some SQL Quries

Static SQL for Insert: Insert into cust(cno, cname, cloc) values (10, “xyz”, “hyd”)

Dynamic SQL for Insert: Insert into cust(cno, cname, cloc) values (v1, v2, v3)

v1,v2, v3 are host variables to be defined in working storage section.

Delete from cust

Delete from cust where cno = 20

Update cust set cname = “ xyz” where cno = 20

Select cno,cname from cust

Select * from cust

Select * from, cust where cno = v1

Select * from cust wehre cno=v1 and cname =v2

Select * from cust where cno between 20 and 60

Select * from cust where cname like “%y%”

Column functions:

Select max(sal) from emp

Select min(sal) from emp

Select avg(sal) from emp

Select sum(sal) from emp

Above statement returns Null values if no row exits for specified condition

To avoid duplicate rows : select distinct cno,cname from cust

To get total no. of rows : select count(*) from cust

Above statement returns Zeros if no row exits for specified condition

SUBQUERY:

Query within Query
First inner query executes & out query executes based on the result of inner query
Max of 15 sub queries can be coded
To simplify sub queries, logic can be built with combination of COBOL + SQL statements

To retrieve second maximum salary from emp table:

Select max(sal) from emp where sal <(select max(sal) from emp)

To retrieve third maximum salary from emp table:

Select max(sal) from emp

where sal < (select max(sal) from emp

Where sal < (select max(sal) from emp))

CO-RELATED SUBQUERY:

For every row of outer query, inner query must executes at least once
First outer query executes & then inner query executes
Practical examples : to fine top 2,3 or n salaries

Select a. sal from emp a

where 0 = (select count(*) from emp b

Where a.sal < b.sal)

-- max
– 2^nd max
– 3^rd max
– 4^th max

DCLGEN :

Declaration Generator . a tool to generates the equivalent COBOL variables.

Which can be used to generate host variables with equivalent data types of DB2 columns.

DB2 Table DCLGEN COBOL

Host variables:

Can be used to pass the data from cobol program to DB2 table or DB2 table to COBOL program.
When host variables are coded with sql statements it must be prefixed with : like :hv-cname.
Table name must be supplied as input to DCLGEN & partition dataset should be as output.
After creating DCLGEN variables which must be copied to application program in WORKING-STORAGE SECTION by using include command i.e.

Exec sql

Inlcude custDCL

End-exec.

Include & copy have the same functionality

SQLCODE :

Predefined numeric number which can be used to check SQL statements for successful , unsuccessful execution.
SQLCODE can be stored in SQLCA(SQL Communication Area)
Copy SQLCA in WORKING-STORAGE SECTION
System defined variable
Evaluate or if statement must be coded to check the SQLCODE immediately after SQL statement.
SQLCODE =00 ---- successful

= +100 --- end of table or record not found.

Sample program:

WORKING-STORAGE SECTION.

EXEC SQL

INCLUDE SQLCA

END-EXEC

EXEC SQL

INCLUDE CUSTDCL

END-EXEC.

01 WS-SQL-CODE PIC S9(4)

88 88-SUCCESS VALUE 00

88 88-NOTFOUND VALUE 100

88 88-FORIENG KEY VOILATION VALUE –532

88 88- MULITPLE ROW VALUE –811

PROCEDURE DIVISION.

UPDATE CUST

SET CNAME = :HV-CNAME

WHERE CNO=:HV-CNO

MOVE SQLCODE TO WS-SQLCODE.

EVALUE WS-SQL-CODE

WHEN 88-SUCCESS

DISPLAY “SUCCESSFULLY UPDATED”

WHEN 88-NOTFOUND

DISPLAY “ RECORD NOT FOUND”

WHEN 88-FOREIGNKEYVOILATION

DISPLAY “ FOREIGN KEY VOILATION”

WHEN OTHER

DISPLAY “ ERROR OCCURRED IN UPDATE”

STOP RUN

END-EVALUATE.

STOP RUN.

CURSOR:

To retrieve multiple rows for a given condition.

Let us take the following example:

Exec sql

Select cno,cname,cloc

into :hv-cno,:hv-cname,:hv-cloc

from cust where cloc =:hv-cloc

end-exec.

If the condition satisfy for one row it executes successfully. If the condition satisfy for multiple rows it wont work. It returns –811 as SALCODE. For this we use cursors.

Cursors can be used to retrieve multiple rows for a given condition
Cursor cycle is Declare ---> Open ----> Fetch -----> Close
Declare: declares or define name for cursor against a table

Can be coded in working-storage section or procedure division

For better readability code in working-storage section.

Open: can be used to open a cursor with rows for a given conditions inbuffer.

Retireves data in to buffer

Must be coded in the procedure division only

Where condition value must be supplied before opening a cursor.

Fetch: can be used to retrieve rows one by one from buffer into application prog.

Which must be coded in procedure divison after open.

Must be coded with hostvariables

No of host variables in fetch & no of columns in the declare must be same

Canbe executed multiple times using perform. i.e. till EOT or record not found which can be identified by SQLCODE = 100

Close : used to close the cursor

Must be coded in procedure division only

Must be executed after open statement.

Practical examples : Can be used to retrieve the data based on loc, date, products.

EXEC SQL

DECLARE C1 CURSOR FOR

SELECT CNO,CNAME FROM CUST

WHERE CNAME=:HV-CNAME

END-EXEC.

EXEC SQL

OPEN C1.

END-EXEC.

PERFORM UNTIL SQLCODE= 100

EXEC SQL

FETCH C1 INTO :HV-CNO,:HV-CNAME

END-EXEC

END-PERFORM.

EXEC SQL

CLOSE C1

END-EXEC

For Update of where current of:

Which can be used to update row by row when multiple rows are satisfied.
Before update cursor has to be declared with for update of column option.
Where current of cursor name option must be used with update command
Withhold: this option can be used to remain cursors open even after commit statement.
Must be coded with cursor statement

EXEC SQL

DECLARE C2 CURSOR WITH HOLD FOR

SELECT CNO,CNAME FROM CUST

WHERE CNAME=:HV-CNAME

FOR UPDATE OF CNAME

END-EXEC.

EXEC SQL

OPEN C1.

END-EXEC.

EXEC SQL

FETCH C2 INTO :HV-CNO,:HV-CNAME

END-EXEC

EXEC SQL

UPDATE CUST SET CNAME=”ABC” WHERE CURRENT OF C2.

EMD=EXEC.

EXEC SQL

CLOSE C1

END-EXEC

INDEX:

Index allows duplicate values
unique index doesn’t allow duplicate rows
cross reference between index table & table is called clustered index.
Create index in1 on cust(cno)

PRIMARY KEY	INDEX	UNIQUE INDEX
Uniquely identified row	Record identified based on the index	Records identified based on the index
No duplicated rows, no null values	Duplicate rows, null values are allowed	No duplicate rows
Can consist of single or multiple columns	Dan consist of single or multiple columns	Can consist of single or columns
This will be stored in SYSKEYS.	This is stored in SYSINDEX	This is stored in SYSINDEX

VIEWS:

CREATE VIEW CVIEW(VCNO,VCNAME,VCLOC) AS

(SELECT CNO,CNAME,CLOC FROM CUST WHERE

CNAME LIKE “%X%)

Logical representation of the table
Stored in virtual memory
Can be derived from single table or multiple tables
Views are updateable if they are derived from single table without any column functions , group by
Multiple views can be generated from single table.
Views are stored in sysviews

Advantages of Views:

Data security
Data correctness
Logical data independence
Part of the information can be visible to the sers
Accessing can be faster.

DELETE RULES:

Delete rules can be applied for delete command against Database.
Delete rules are 3 types

UNION:

UNION is used to concatenate rows into one table from single or multiple tables.
Rules : no. of columns & data type of both the queries must be same. column may be different
UNION can be used to eliminate duplicate rows
UNION ALL retrieved duplicate rows also.

SELECT CNO,CNAME FROM CUST WHERE CNO=10

UNION/UNIONALL

SELECT CNO,CNAME FROM ITEM WHERE INO=20

JOINS:

JOINS can be used to concatenate columns from one table or multiple tables.
JOIN types are :

EXPLAIN :

It can be used to evaluate the performance of SQL queries.

It can be used to tune SQL queries.

Input is SQL queries and output is plan-table.

For every SQL query one plan-table will generate.

All plan-tables are stored in physical seq file.

Plan table

Query

block no

Table

Name

No. of

Cols

index

no.of

indexs

owner

join type

groupby

Cpu

time

Cust

In1

Custc

Self

10 min

DB2 CATALOG:

Consists of Table pace, Index space, Index, unique index, Views, Alias, synonyms, keys.
When we create table, the details of table are entered in Systable automatically.

SysIBM.SYSTABLE

Table Name	No.of cols	Owner name	Created by	Created date	Created time
Cust	10	Abc	Xyz	02-apr-2004	0850
Item	15	Mno	Rst	06-apr-2004	1020

SysIBM.SYSINDEX

Index name	Table Name	No.of cols	Owner name	Created by	Created date	Created time
In1	Cust	10	Abc	Xyz	02-apr-2004	0850
In2	Item	15	Mno	Rst	06-apr-2004	1020

SysIBM.SYSCOLS

Col name	Table name	Index name	Primary key	Foreign key
Cno	Cust	In1	Cno	-----
Cname	Cust	In1	Cno
Cloc	Cust	In2	cno
Ino	Item		Ino	Cno
Iname	Item		Ino	Cno
Ides	Item		Ino	cno

SysIBM.SYSKEYS: All primary & foreign keys.

Grant table syscols to all

Grant table syscols(select/delete/update) to user1,user2

Revoke table syscols from all.

CATALOG:

SYSTABLE, SYSCOL, SYSKEYS, SYSINDEX, SYSPKS, SYSFKS, SYSALIAS, SYSSYNONYMS, SYSINDEX, SYSVIEWS,SYSTABLESPACE, SYSINDEXSPACE.

PRECOMPILATION PROCESS:

Pre compiler takes COBOL+DB2 program as input & generates DBRM which will be stored in userdefined PDS as separate member of Recln=80

DSNHPC --- IBM supplied utility used for precompilation.

Precompiler functions:

Separates SQL & COBOL statements
Check SQL syntaxs
Replace all SQL statements with host language call statements in the compiled program.
Which generates timestamp tokens

BIND:

BIND takes DBRM as input & generate package & application plan. The package will be loaded to the directory. Plan will be loaded to sysplans.

Bind functions:

Checks authorization for SQL statement
Checks the syntax errors of SQL statements like

Missing co name in the select list & used in order by & group by
Mismatch columns host variables
Data type mismatch of columns & host variables
Indicator variables not declared
Data truncation.

BIND SUBCOMPONANTS/PARAMETERS:

OPTIMIZER:
- It generates optimized access path by analyzing the statistics of SQL statements which will be stored.
- RUNSTATS utility is one of the ISPF panel option which is stored in DB2 defaults option.
- Optimized path is stored in package which is not executable module.

ISOLATION LEVEL:

Which can be used to lock the database at the time of executing SQL statements.

Cusrsor stability(CS): It acquires the address of a row. Sets the pointer to a specified row based on SQL query & acquires the lock against that row. Then releases the klock after the transaction before commit.

Repeatable Read(RR): which acquires the address of a row & acquire lock against the page(1 page -4024 bytes) & then released the lock after the commit statements.

Default is RR.

RUNTIME SUPERVISOR:

Which is to oversee execution of SQL statements.

Statistics like no of tables, columns, indexes, keys

PLAN/APPLICATION PLAN:

It consists of executable module which is actual output of SQL statements which must be specified in the RUNJCL to execute SQL queries if the program is batch program. If the program is online which must be specified in RCT. Application plan will be loaded to load module with time stamp tokens.

COBOL COMPILATION:

The compiler takes COBOL statement as input to generate object program, & loaded to the load module by line/edit with time stamp tokens.

UTILITIES USED:

DSNHPC : system utility pre compiler.

IKJEFT01 or IKJEFT01B --- BIND /Run Cob-DB2 Program

IGYCRCTL or IKFCBLOO --- COBOL compilation

IEWL or HEWL --- link/edit

INTERVIEW QUESTIONS: (watch this space for more info on below Qns)

What is RI ? where did u use RI in your project? Explain with example?
What is the difference between primary key, foreign key, index & unique index?
Can we create index when table has duplicate rows?
Can we create unique index when table has duplicate rows?
Can we create index or unique index on empty table?
What happens to the package when index is dropped? What’s the process or steps to be taken?
How to delete package?
Where package is stored? How to retrieve package?
Difference between plan & package?
What are the steps to be taken when SQL statements are changed without changing any COBOL statements?
Do we need to pre compile when index is dropped?
Can we bind when table is dropped?
Can optimized access path consist of multiple DBRMS.
What is the significance of timestamp tokens?
What is the significance of normalization?
Where do we specify run program, plan name, library & DB2 subsystem
IBM supplied utility to run COBOL + DB2
What is difference between DB2 & COBOL files & give some example for COBOL files & DB2 tables related to your project?
Can we load data from sequential file to table or table to sequential file?
What are the steps to be followed to develop DB2+ COBOL program?
Can we prepare a program/compile when DB2 system is down?
How to identify DB2 test or production system by seeing run JCL?
What is the output of explain?
What is the difference between correlated sub query & sub query?
How to find 4^th max sal?
How to find nth max sal?
How to evaluate SQLcodes & where it is stored?
How to count total no of unique rows from the table?
How to sum repeated rows?
How to write a cobol program for above query? Retrieve row by row & use cobol logic to sum repeated rows?
What is the significance of DCLGEN?
Where DCLGEN is stored?
Difference between join & union?
difference between UNION & UNIONALL?
Can be have different col names in union?
How do u evaluate/tune/analyze SQL queries?
What are the JCL utilities for compile, pre compile, bind & link edit?
Wha5t is the significance of isolated levels?
Can we alter foreign key?
Can we alter primary key?
Can we alter data type & length?
What are the equivalent cobol variables for varchar?
What is the time stamp & its format?

Top 10 DB2 SQL quries

2008-02-04T09:40:00.000-08:00

RETRIEVE ONLY DUPLICATE ROWS FROM A TABLE:

SELECT A, B, C
FROM TABLE_A
GROUP BY B
HAVING COUNT(*) > 1

QUERY:

IF THE CHAR FIELD HAS FIRST DIGIT AS NUMBER
E.G. 1AA THEN THE VALUE SHOULD BE CONSIDERED AS '1'
12A THEN THE VALUE SHOULD BE CONSIDERED AS '12'
123 THEN THE VALUE SHOULD BE CONSIDERED AS '123'
A11 THEN THE VALUE SHOULD BE CONSIDERED AS '0'

ANSWER:

SELECT

CASE

SUBSTR(F1,1,3) BETWEEN '000' AND '999'

THEN INTEGER(SUBSTR(F_1,1,3))

WHEN SUBSTR(F1,1,2) BETWEEN '00' AND '99'

THEN INTEGER(SUBSTR(F_1,1,2))

WHEN SUBSTR(F1,1,1) BETWEEN '0' AND '9'

THEN INTEGER(SUBSTR(F_1,1,1))

ELSE 0

END

AS OUT

How to get the MAX count when group by some other field

SELECT USER_UPDATED, COUNT (USER_UPDATED) AS COUNT1

FROM CGDEV4.TWC39

GROUP BY USER_UPDATED

HAVING COUNT (USER_UPDATED) >= ALL

(SELECT COUNT (USER_UPDATED)

FROM CGDEV4.TWC39

GROUP BY USER_UPDATED);

How to Subtract the years, months and days separately from DATE

SELECT DATE ('2003-10-13') - DEC ('03') YEARS - DEC ('07') MONTHS - DEC ('13') DAYS FROM SYSIBM.SYSDUMMY1;

SELECT DATE ('2003-10-13') - DEC ('2000') YEARS –

DEC ('27') MONTHS - DEC ('45') DAY

FROM SYSIBM.SYSDUMMY1;

How To eliminate duplicate records except one

DELETE FROM TZCHC TD1

WHERE HOLIDAY_DATE<> (SELECT MIN (HOLIDAY_DATE)

FROM TZCHC TD2

WHERE TD1.LONG_DESC = TD2.LONG_DESC

AND TD2.LONG_DESC = 'CHRISTMAS DAY’);

How to find N th max or min

SELECT DISTINCT A.TIMESTAMP_UPDATED, A.USER_UPDATED

FROM CGDEV4.TWC39 A

WHERE 5 =

(SELECT COUNT (DISTINCT B.TIMESTAMP_UPDATED)

FROM CGDEV4.TWC39 B

WHERE B.TIMESTAMP_UPDATED >= A.TIMESTAMP_UPDATED)

How to Execute the SQL through JCL using utility

//CREATE EXEC PGM=IKJEFT01,DYNAMNBR=20

//SYSTSPRT DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SYSTSIN DD *

DSN SYSTEM(DB2T)

RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) LIB('DB2T.RUNLIB.LOAD')

END

//SYSIN DD *

SELECT COUNT(USER_UPDATED)

FROM CGDEV4.TWC39

WHERE USER_UPDATED='DPPLSPP'

GROUP BY USER_UPDATED

How to count occurrence of letter in a string

SELECT (LENGTH('SUBASH') - LENGTH(REPLACE('SUBASH','S',''))) AS S_COUNT FROM SYSIBM.SYSDUMMY1

How to delete duplicated based on certain column retaining one

DELETE FROM TABLE1 A WHERE A.PRIMARYKEY<>(SELECT MIN(PRIMARYKEY) FROM TZCHC B WHERE A.COLUMN1= B.COLUMN1);

Correlated Sub Query

SELECT A.DRIVER_REFERENCE

FROM CGDEV4.TU4ML A

WHERE A.LICENSE_NUMBER = 'POTAPA*355B2' AND

A.DRIVER_REFERENCE =

(SELECT C.CLIENT_REFERENCE

FROM CGDEV4.TZ1SR C

WHERE A.DRIVER_REFERENCE = C.CLIENT_REFERENCE AND

SUBSTR(C.Z_UPPER_FIRST_NAME,1,1) = 'A' AND

SUBSTR(C.Z_UPPER_LAST_NAME,1,3) = 'JZB')

How to get the value with leading zeros

SELECT POLICY_NUMBER, DIGITS (BASE_PREM_TOT)

FROM DB2K.CGIST.TW4T0

If the value of BASE_PREM_TOT is 472.00 then it will be selected as like this 00000047200

How to get the characters after space including first character with in one string?

SELECT SUBSTR (LAST_NAME_COY_NAME, 1, 1)

SUBSTR (LAST_NAME_COY_NAME, POSSTR (LAST_NAME_COY_NAME,' ') +1, 1)

SUBSTR (LAST_NAME_COY_NAME,

LOCATE (' ', LAST_NAME_COY_NAME, POSSTR (LAST_NAME_COY_NAME,' ') +1) +1, 1)

FROM CGDEV4.TZ1SR WHERE CLIENT_REFERENCE <= '0000000001';

Here POSSTR (LAST_NAME_COY_NAME,' ') +1 will give the position after space.

LOCATE (' ', LAST_NAME_COY_NAME, POSSTR (LAST_NAME_COY_NAME,' ') +1) +1, 1)

Will Returns the starting position of the first occurrence of expression1 within expression2. If the optional expression3 is specified, it indicates the character position in expression2 at which the search is to begin. If expression1 is not found within expression2, the value 0 is returned

How to get the fields which contain low values OR high values at any place

SELECT ADDR

FROM TABLE1

WHERE LENGTH (ADDR) > LEBGTH (REPLACE (ADDR, X’00’,’’) ;

If you want high values then replace X’00’ with X’FF’

For best performance you can use this.

SELECT TOWN_SUBURB

FROM DB2T.CGDEV5.TZ1SR

WHERE TOWN_SUBURB LIKE '% %'

FETCH FIRST 20 ROWS ONLY

After writing this, go to command prompt and turn on hex. I.e. HEX ON

Then you will get like this

DCDC4764674 39250DC0CD0

Go and change above 4 (highlighted in red color) as 0 and save the changes. Then it will become low value in that position. Then turn off hex. I.e. HEX OFF

Then it will select the all TOWN_SUBURB fields which contain low value at any place

How to get seat_no from which continuously 4 seats available

SELECT A.seat_no

FROM Table1 A

WHERE A.availabilty = 'Y'

AND EXISTS

( SELECT COUNT(*) FROM Table1 B

WHERE B.seat_no BETWEEN

A.seat_no AND A.seat_no + 2

AND B.availability = 'Y'

HAVING COUNT(*) = 4 )

FETCH FIRST 1 ROWS ONLY WITH UR

How to left pad?

SQL>create table test( id number);

SQL>insert into test values(1);
SQL>insert into test values(2);
SQL>insert into test values(3);
SQL>insert into test values(4);

SQL> select * from test;

ID
----------
1
2
3
4

SQL> select id,lpad(id,8,'00') from test;

ID LPAD(ID,
---------- --------
1 00000001
2 00000002
3 00000003
4 00000004

Syntax of lpad function is Lpad(String, max length, character to be padded);

SQL> select id,lpad(id,8,'0') from test;

ID LPAD(ID,
---------- --------
1 00000001
2 00000002
3 00000003
4 00000004

Interview Questions @ EMphasis

2008-01-31T11:42:00.000-08:00

Cobol,Jcl,Db2 related Interview Qns

How to identify a file declared in Source (cobal pmg only) is a VSAM file (ESDS.) or not.
1. AS(Address Sequential ) as to prefixed to the logical name
  1. Eg :: select File1 assign to AS-FILE11
2. Check the FD section :: we declare ORGANISATION IS SEQUENTIAL/INDEXED/ RANDOM
3. File status code (23 eof)

How do create VSAM files
1. Using IDCAMS / File Aid / Workbench (these tools internally uses idcams)

How do u identify a file to be created is ESDS/KSDS/RRDS – by looking at Cluster create statements .
1. ESDS will have :: Data and Cluster Datasets
2. KSDS will have :: Index,Data and Cluster Datasets
3. RSDS will have :: Data and Cluster Datasets

Other Important Point is in Cluster Declaration, we declare Index, Non-Indexed

and Numbered for KSDS/ESDS/RRDS

How do u identify whether VSAM file is Fixed or VB.
1. If Record Size (F,F) -- > fixed, if Recordsixe (F,V) -- > Variable

How do you identify the dataset is VSAM dataset or not by looking at the Job.
1. Check the DCB parameter, for DSORG parameter

DSORG = PS (seq.dataset)

PO ( Partitioned dataset)

ESDS (esds vsame dataset)

KSDS (ksds vsame dataset)

RRDS (rrds vsame dataset)

How do u copy records from 50 to 70 from a file having 100 records (for various vsam files).
1. Use Repro with ESDS :: From (address) To (address)
2. KSDS :: From (Index key) To (Index Key)
3. RRDS :: From (Number) To (Number)
4. LSDS :: Skip (nn), count(nn)

How do u declare a Variable file in Cobol -- check roy and datidar
1. Eg :: FD File1

Record Contains 10 to 1000 characters

WS-Rec-MIN PIC X(10).
01 WS-Rec-Max PIC X(1000)

how do u declare LRECL in JCL for Variable file.
1. For above file lrcel = 1004 (i.e., 4 bytes additional to actual size of file)

When do use Optional file – while declaring a file and where do u mention this.
1. We use this in IO-section,
2. The job will run even when the file is available or not.

Eg : :: select File1 assign to AS-FILE11

File1 is Optional

When do we use Extend Mode for the seq file,
1. This mobe is used to append the data. Usually it is used to record history.

Can cobol pgm can be coded without FD section ?
1. Yes.
2. Eg : Usually most of the subprograms or stored procedures will not have FD section

D in 7^th column of cobol program
1. This is used in the program for debugging mode

SOC4 :: trying to read a will which is not opened or index/subscript out of range (it basically happens while trying to access the data)

SOC7 :: Dataismatch (will give the statement of the process statement. (like IF ws-n > 0) or airthamatic statements or statemts involves evaluation etc.,) Note :: Move statement is root cause for SOC7 error Resolution :: a) get the error statemnt no. from the sysout of run-jcl b) search for the statement no. in sysprint of Compile jcl log -- this will give you the approxmate statement caused soc7 error, c) Find for the variable with comp3 in above statement, and find the cause using by displaying respective variables or using Expdeter

Pls write the jcl to run main program which has a subroutine?

Main Pgm::

Select INFILE-MAIN assign to MAINFL

SUB Pgm ::

Select INFILE-SUB assign to SUBFL

JCL ::

//job

//step exec PGM=MAINPGM

//MAINFL DD DSN=a.b.main

//SUBFL DD DSN = a.b.subp

What is implicit and Explicit Commit
1. Explicit commit is coded by the programmer, as and when specified commit point is reached data is commited, this is done to minimize the data loss in case of abends.
2. Implicit Commit is taken care by the system on successful/unsuccessful execution.

I have 200 versions of a GDG, how to include all the versions in a single step.
1. Use the GDG base, It will include all the versions (including the one created in above step)

In step1 I have created a version with +1, how do crete 2^nd version in next step
1. Give it at (+2)

Now I want to use the file created in step to in step 3, how do u refer that
1. Give it as (+2) as we are in same Job

How to Pass return Code / data to JCL from COBOL
1. We can pass only two bytes of data from cobol to JCL using the RETURN-CODE.
2. Eg : Move 04 to RETURN-CODE. This return code will override the actual return code of previous step.

which file is required, to expedit(debug a program thru expeditor) a program
1. Its DDIO (RRDS ) file (to generate this file pgm has to be compiled with Exped=y option)

Mainframe Written test @ Accenture

2008-01-29T10:02:00.000-08:00

1. What r the 2 function we can get by using INSPECT verb? (c)

A. file handling, error handling

B. opening a file, closing a file.

C. char counting, replacing.

D. none of the above

2. Indicate which of the following is not true about the formal parameters in a subroutine?(c)

A. every formal parameter must appear in the linkage section of the subroutine

B. every formal parameter must appear in the using phrase of the procedure division header of the subroutine

C. A formal parameter name can appear more than once in the using phrases of the procedure division header of the subroutine.

D. A formal parameter can not be declared with value clause in a subroutine

3. What is the value of A? (B)

01 A pic 9(3).

Move 100 to A.

Perform 1000-para thru 1000-exit.

Display the value of a=A

Stop run.

1000-para.

If A=100

Perform 1000-exit

Else

Move 150 to A

End-if.

Move 200 to A.

1000-exit.

Exit.

a.100

b.200

c.150

d. none of the above is correct.

4. What is the value of B? (A)

Move 1 to A

Evaluate true

When A>0

Move 20 to B

When A=1

Move 10 to B

When A<1

Move 30 to B

End - evaluate.

A. 20

B. 10

C. 30

D. none of the above is correct

5. In file handling what is the meaning of access mode DYNAMIC? (B)

a. Data can b accessed only sequence

b. Data can b accessed both sequence and rand

c. Data can b accessed only rand

d. This is not correct access method

6. Which of the following is not true about the block contains clause? (C)

a. It reduces the space required to store a file on tape or disk

b. It reduces the time required to process a sequence file

c. It reduces the number of logical records in a file

d. It reduces the number of physical records in a file

7. All the following statements are false about the merge verb except one. Identify this

a. Can merge a max of 3 files? (C)

b. Does not require that the i/p files to b merged b sorted on the merge keys.

c. Requires that all files mentioned in the statement must have records of same size

d. Does not require that the positions of the merge key with in the record descriptions of each of the files must b same.

8. to prevent DB2 from closing a cursor after a commit operation, which of the following DECLARES CURSOR statements is used? (B)

A. for update

B. with hold

C. where current of

D. with commit of

E. none of the above is correct

9.Wcich of the following delete rules will not allow a row to b deleted from the parent table if a row with corresponding key value still exists in the child table?(C)

A. delete

B. cascade

C. restrict

D. set null

E. all of the above is correct

10. Physically the DB2 data is stored in which data sets?(C)

A. sequential datastes

B. hierarchical

C.VSAM

D. partitioned

11. How would you determine you are at the end of the result set? (C)

A. check the NULL indicator host variable after each fetch

B. check the SQLCA after closing the cursor

C. check the SQLCA after each fetch

D. checks the SQLDA after each fetch.

12. An update lock gets released by an application using the repeatable read isolation level during which of the following? (D)

a. If the cursor accessing the row is closed.

b. If the transaction issues a ROLLBACK statement.

c. If the cursor accessing the row is moved to the next row.

d. If the transaction changes are made via an UPDATE statement.

13. Which of the following options can b used to place the cursor on the screen thru the program? (C)

A. By setting IC position in the map set.

b. Moving -1 to field attribute

c. Moving -1 to field length

D. none of above

14. Which one of the following can b used to register a TDQ? (C)

a.PPT

b.PCT

c.DCT

d.FCT

15. Find out the correct option from the following? (C)

1. Physical map is a load module

2. Symbolic map is a load module

3. Physical map is a program name

4. Symbolic map is a map copybook

a.1&2

b.2&3

c.1&4

d.all of the above

16. How can a transaction b initiated from the program? (B)

A. by giving program name

B. by using START command in the program

C. by using LINK

D. by using CALL

17. Which one of the following is related to MDT?(D)

A. fset & feset

B. reset & frset

C. reset & modify

D. fset &frset

18. Which will override in steplib & joblib : StepLib

19. what will be the COND parameter for executing a step, even if any of the previous steps terminated abnormally?

Cond=even

20. which of the following options is true, for getting xclusive control of the dataset?

Disp=old

21. Select the wrong choice? (D)

A. IDCAMS is utility of access method services, used to define VSAM clusters

B. REPRO is used to copy records from VSAM file to non-VSAM file and vice versa

C.LISTCAT utility is used to use to find the details like NAME, HISTORY, VOLUME, and ALLOCATION of the any existing VSAM dataset.

D. records of VSAM files generally stored in blocks not in control intervals

22. for which of the following file type, will the control interval split occur? (A)

A. ksds

B. esds

C. rrds

D. All of the above.

23. Select the correct answer? (D)

A. records from KSDS can b deleted physically

B. records from ESDS can not b deleted physically

C. RRDS does not support spanned records.

D. all of the above is correct

24. When do we encounter the status code ‘GA’ for IMS database? (B)

A. At the end f the database.

B. The next higher level segment was retrieved during sequential retrieval.

C. A segment of different type has been retrieved.

D. The segment field has been changed in the program’s IO area.

25. Which of the following is true? (A)

A. A qualified call should have an SSA and an unqualified call doesn’t have any SSA

B. A qualified call should not have an SSA and an unqualified call should have an SSA

C. A qualified call and an unqualified call both should have an SSA.

D. none

For Technical Interview Questions : keep watching this blog.

IKJEFT01 – Terminal Monitor Program (TMP)

2008-01-27T12:51:00.000-08:00

IKJEFT01 – Terminal Monitor Program (TMP)

We can Invoke DB2 subsystem in three ways

1. SPUFI

2. QMF and

3. IKJEFT01 --- Here we discuss more about this TMP.

IKJEFT1A and IKJEFT1B are not utilities but are rather two alternate entry points for the TSO Terminal Monitor Program initialization routine, IKJEFT01. The DB2 utilities were written to run under TSO, either under a terminal session or in batch.

If you have access to the JCL used to start your TSO sessions, you will see that on the // EXEC statement, PGM=IKJEFT01 most likely (or, in some cases, ADFMDF03, which is a part of TSO/E called the Session Manager which, at shops enlightened enough to run it, calls IKJEFT01 in turn). When running the TMP in batch, in order to have TSO execute a series of TSO commands asynchronously, you can use PGM=IKJEFT01, IKJEFT1A or IKJEFT1B.

The following is taken from the TSO/E User's Guide for z/OS 1.4, section 3.4.4.1.2:

“PGM= “ specifies the module being executed. In addition to IKJEFT01, there are two other entry points available for background execution that provide additional return code and abend support. The differences among the three entry points are:

PGM=IKJEFT01 ::

When a command completes with a non-zero return code, the program goes to the next command. When a command abends, the step ends with a condition code of 12 (X'C').

PGM=IKJEFT1A ::

If a command or program being processed by IKJEFT1A ends with a system abend, IKJEFT1A causes the job step to terminate with a X'04C' system completion code.
IKJEFT1A also returns to the caller the completion code from the command or program in register 15.
If a command or program being processed by IKJEFT1A ends with a user abend, IKJEFT1A saves the completion code in register 15 and then terminates.
If a command, program or REXX exec being processed by IKJEFT1A returns a non-zero return code to IKJEFT1A, IKJEFT1A saves this return code in register 15 and then terminates.
Non-zero return codes to IKJEFT1A from CLISTs will not affect the contents of register 15 and the TMP will continue processing.
For a non-zero return code or an abend from a command or program that was not given control directly by IKJEFT1A, no return code is saved in register 15, and IKJEFT1A does not terminate.

For more info refer to :: http://www.teradataforum.com/l030315a.htm

DB2 - CHECKPOINTING & RESTART

2008-01-27T12:07:00.000-08:00

CHECKPOINTING & RESTART IN BATCH PROGRAMS IN CASE OF ABENDS

The Concept of Check pointing is to Save the data permanently in the database till the Last Checkpoint in case of an Abend in the Program after taking a Checkpoint. In the case of an abend the DBA will automatically back out (reverse) the changes made to the Database after the Checkpoint. So if we RESTART the Program after an abend, the program will use the Last Check pointing Information for restarting. There are various ways for Checkpoint/Restart.

1. With the Help of a Restart File: -

This type of Check pointing will need a Restart File. In this type, while taking each Checkpoint we will store the Checkpoint ID, with the Necessary Save Area in CC5DAP database. The Checkpoint ID will be usually the Time at which that particular Checkpoint was taken. For Restarting this type of Programs, there will be two Steps.

The first step will put the Stored CC5DAP data corresponding to the Last Checkpoint to a Restart File.

The Second Step will read this Restart File, correctly gets positioned in the Database where the Last Checkpoint was taken (for e.g.:- if it is a Sequential execution in a database), and starts Execution from that point.

2. With the Help of a BMP(Batch Message Processing) Record:-

This is the Latest Technique being used for taking Checkpoint. In this case NO Restart File or CC5 Database is required for Restarting the Program in case of an abend. It uses a BMP Record to get back all the information about the last Checkpoint.

Here the Checkpoint Id will be 8 characters (4 character Program Identification ID + Sequence Id).

For e.g.: - if your Program is BMD001CP, you can put the Program Identification ID as 'BMD1'. The

Sequence Id contains the number of the Last Checkpoint.

(e.g.:- if it is the 19th Checkpoint, Sequence ID will be 0019).

During Restart of the Program, it will automatically read the corresponding BMP Record and gets all stored information regarding the Last Checkpoint.

2008-01-26T10:35:00.000-08:00

Oracle 11i Applications Architecture

The Oracle Applications Architecture is a framework for multi-tiered, distributed computing that supports Oracle Applications products. In this model, various servers are distributed among multiple levels, or tiers.

Oracle Applications Architecture

The Desktop Tier

The client interface is provided through HTML for the newer HTML-based applications, and via a Java applet in a Web browser for the traditional Forms-based interface.

Forms-based Desktop Tier Architecture

In Oracle Applications Release 11i, each user logs in to Oracle Applications through the E-Business Suite Home Page on a desktop client web browser.

Forms Client Applet

The Forms client applet is a general-purpose presentation applet that supports all Oracle Applications Forms-based products, including those with customizations and extensions. The Forms client applet is packaged as a collection of Java Archive (JAR) files. The JAR files contain all Java classes required to run the presentation layer of Oracle Applications forms.

Oracle Jinitiator

The Forms client applet must run within a Java Virtual Machine (JVM) on the desktop client. The Oracle JInitiator component allows use of the Oracle JVM on web clients instead of the browser’s own JVM. JInitiator is implemented as a plug-in (Netscape) or ActiveX component (Microsoft Internet Explorer).

The Forms client applet and commonly used JAR files are downloaded from the Web server at the beginning of the client’s first session. Less commonly used JAR files are downloaded as needed.

The Application Tier

The Application tier has a dual role: hosting the various servers that process the business logic, and managing communication between the desktop tier and the database tier. This tier is sometimes referred to as the middle tier. Oracle9i Application Server (9iAS) provides the technology used on the application tier. Six servers comprise the application tier for Oracle Applications:

• Web server

• Forms server

• Reports server

• Discoverer server (optional)

• Concurrent Processing server

• Admin server

Try to avoid using a mixture of different platforms on your application tier. This makes maintenance easier, since only one set of patches needs to be downloaded.

Load Balancing

The application tier supports load balancing among many of its servers to provide higher availability, fault tolerance, reliability, and optimal scalability. If you have more than one of any of the following types of server, load balancing can be employed:

• Web server

• Forms server

• Reports server

• Discoverer server

• Concurrent Processing server

Typically, load balancing is most commonly used to spread the load across multiple Web servers, where the load may vary significantly.

For More Information : Keep watching this blog

2008-01-24T12:01:00.000-08:00

Tips for Telephone interview:

Telephone contact means that there are no visual clues unlike a face-to-face interview, In a telephone interview you have to sell yourself using only words and the tone of your voice

Smile! Amazing as it might sound, smiling whilst you talk really helps. You will come across as more friendly and confident. Try it!

Few Minutes Before Telephonic:

Should have compile list of :

• Your strengths and weaknesses
• Your background and skills,

• Answers to typical interview questions

Make sure you have :

• Keep your resume in clear view, so it's at your fingertips when you need to answer questions.

• Have a pen and paper handy for note taking.

• Turn mobile phones off, so your call isn't interrupted

• Practice reducing "ums" and "uhs" and "okays"

During the Interview:

• The first 15 seconds are crucial and interest in your voice is the key. Just the way you answer the phone has an impact on the caller. Talk distinctly and with confidence.

• Smile! Smiling whilst you talk really helps. You will come across as more friendly and confident.

• Use gestures as in normal conversation and be enthusiastic where appropriate

• The tone and rhythm of your voice are important. If you speak in a monotone this will be more apparent on the telephone.

• The interviewer may start with the question, "Tell me about yourself." You should be able to explain the value you added to the company.

• Review a problem that you turned into a positive situation for each position that you list. If possible try to quantify accomplishment in each position

• Give short answer to questions. Short sentences are more easily understood over the phone than long orations. They also give more opportunity for interchange between you and the interviewer, which helps both of you maintain your interest

Listen to Interviewer carefully :

• When the interviewer speaks give him your undivided attention

• Don’t look at your notes or think of anything else during that time

• Don't answer a question that you haven't fully understood.

Restate the question.

• This will also give you time to prepare an answer and prevent long silences on the phone.

• Don't do this too often, as the interviewer will quickly become tired of repeating himself or herself.

Answer questions courteously.

• Try to sound relaxed and confident.

• A note of irritation or frustration in your voice is much more noticeable over the phone, and can't be offset by positive body language.

• Try not to be put off by pauses from the interviewer - (s)he may be taking notes .Don’t feel compelled to fill in pauses with some comments or questions.

Strengths and Weaknesses.

• You must be able to specify your major strengths.

• Your weaknesses, if such must come up, should only be turned around to positives.

Donts :

• Rambling,

• Interrupting the interviewer

Winding up…

• The end of the call is always a tricky thing. A good suggestion is to thank the caller for his or her time.

• If you don't already have it, be sure to ask for the interviewer's exact title and name spelling, along with a email address, so that you can send a thank you note.

Best of Luck • • •

DB2 Utilities and Commands (helpful for DB2 Certification)

2008-01-23T01:26:00.000-08:00

• Utilities
– Image Copy
– CDB SuperCopy
– Quiesce
– Load
– CDB SuperLoad
– Check
– BSCTIAUL
– CDB SuperUnload
– Recover
– CDB SuperRestore
– Reorg
– CDB SuperReorg
– Runstats
– Report Recovery
– Repair

• Commands
– Display/Stop/Start Database
– Display/Term Utility

DB2 Logging

• DB2 Logs
– A ‘journal’ of all activity for a subsystem
– Undo/redo records for table updates
– Each event in the log is identified by its RBA(relative byte address)
– RBA denotes a point in time: the greater the RBA, the later in time the event occurred
– Data Sharing - LRSNs along with RBAs

– Some events:
• Full image copy
• Quiesce
• Load

Data Sharing

• Benefits of Data Sharing
– High availability
– Workload balancing
– Sysplex parallelism
– Better use of resources

Backing Up Tables

• Image Copy
– Backup for DB2 table spaces
– Can also copy index spaces
– Can perform full (all data) or incremental (changed data) copies
– Can prevent or allow updates by others during copy
– SAMPLIB: DB2COPY

• CDB SuperCopy
– Faster method of creating standard DB2 image copies
– Can back up several spaces with single command, via generic name
– Can stack multiple copies on one tape
– Supports dynamic allocation of image copy tape files
– SAMPLIB: CDBCOPY

The Quiesce Utility

• Quiesce
– Sets a ‘bookmark’ in the DB2 log,indicating the RBA at which NO activity is occurring on the specified table space(s)
– Use this utility to produce an RBA that can later be used for a point-intime (PIT) recovery of the table space(s)
– Several spaces can be quiesced in a single request
– Runs very quickly
– Use this in production wherever you want to establish a possible recovery point
– SAMPLIB: DB2QUIES

Loading Tables

• Load
– Loads data into DB2 table from an external QSAM file (tape or disk)
– Can selectively include or exclude input records
– Can replace all rows, or add to existing rows
– Can enforce RI or ignore it (if ignored, RI must be verified via Check utility)
– SAMPLIB: DB2LOAD

• CDB SuperLoad
– Faster way to load a DB2 table
– Automatic image copy, if desired
– Enhanced data conversion
– Load into multiple tables, from multiple input files
– SAMPLIB: CDBLOAD

Integrity Utilities

• Check
– Verifies all RI on a table space
– Verifies table constraints
– Resets ‘check pending’ flag, usually set by Load utility, if constraints were not enforced
– Will insert all invalid rows into a separate table for review, if requested
– SAMPLIB: DB2CHECK

Unloading Tables

• BSCTIAUL
– Modified version of IBM-supplied sample program DSNTIAUL
– Uses dynamic SQL to unload table(s) to sequential file(s)
– Also generates DB2 Load utility control statements
– Can use output to (re)load table

– Uses:
• Data transfer (e.g., production to test)
• Data archival
• Simple data editing
– SAMPLIB: BSCTIAUL

• CDB SuperUnload
– Quick way to unload table data to sequential file
– Can unload from:
• Table itself
• Full/partial image copy
• DSN1COPY file
– Can unload subset of data via extensive filtering options
– Can sort output
– Generates Load utility statements
– SAMPLIB: CDBUNLD

Data Reorganization

• Reorg
– Reorganizes table and/or index spaces
– Use to put certain table/index space changes into effect (e.g., PRIQTY,SECQTY, SEGSIZE, etc)
– Deletes and reallocates underlying datasets
– Can use as unload - Unload External
– Can delete rows
– SAMPLIB: DB2REORG

• CDB SuperReorg
– Faster Reorg
– SAMPLIB: CDBREOR

Statistics and Reporting

• Runstats
– Updates DB2 catalog with current statistics regarding table space, table, column and index information
– These statistics are used by the optimizer to determine access paths
– Plans/packages should be rebound to take advantage of any updated information
– SAMPLIB: DB2STATS

• Report Recovery
– Provides listing of all recovery-related events for table space(s)
– Use to identify datasets needed for recovery
– SAMPLIB: DB2RPTRC

• Repair
– Used to reset the following flags:
• Copy pending
• Recovery pending
• Check pending
– Also used by DBA to fix broken pages
– SAMPLIB: DB2REPR

Recovery Utilities

• Recover
– Restores table space to consistent state,using log and/or image copy files
– Recover to currency: useful if table space was modified outside of DB2
– Point-in-time (PIT) recovery:
• To specific RBA
• To specific image copy
• Separate index recovery required
– SAMPLIB: DB2RECOV

• CDB SuperRestore
– Faster recovery
– Recover test option
– SAMPLIB: CDBRSTR

DB2 Commands

– DISPLAY DATABASE
– STOP DATABASE
– START DATABASE
– DISPLAY UTILITY
– TERM UTILITY

• Syntax:
-command options
Ex. -DISPLAY DB(CLAS01DB)
• Issue via:
– DB2I, Option 7
– Platinum DB2 Command Processor (DB2C)

Display Database

• DISPLAY DATABASE
– Displays database and optionally table/index space status
– Use to identify any spaces in any kind of restricted mode (e.g., copy pending,recovery pending, etc)
– May help identify cause of -904 errors

• Syntax
-DISPLAY DATABASE(dbname)
SPACENAM(space-name)
LIMIT(*) RESTRICT

Result on Screen

• Partial output from display database command
– All index / table spaces are in RW status
-DIS DB(CLAS01DB) SPACE(*)
DSNT360I -DB2T ***********************************
DSNT361I -DB2T * DISPLAY DATABASE SUMMARY
* GLOBAL
DSNT360I -DB2T ***********************************
DSNT362I -DB2T DATABASE = CLAS01DB STATUS = RW
DBD LENGTH = 4028
DSNT397I -DB2T
NAME TYPE PART STATUS
-------- ---- ---- ------------------
MKDEPTTS TS RW
MKEMPTS TS RW
MKDEPTXB IX RW
MKDEPTX1 IX RW
MKDEPTX2 IX RW
MKEMPXB IX RW
MKEMPXB IX RW
MKEMPX1 IX RW
******* DISPLAY OF DATABASE CLAS01DB ENDED *******

Result on Screen

• Partial output from display database command
– Some spaces are in restricted mode
-DIS DB(CLAS01DB) SPACE(*)
DSNT360I -DB2T ***********************************
DSNT361I -DB2T * DISPLAY DATABASE SUMMARY
* GLOBAL
DSNT360I -DB2T ***********************************
DSNT362I -DB2T DATABASE = CLAS01DB STATUS = RW
DBD LENGTH = 4028
DSNT397I -DB2T
NAME TYPE PART STATUS
-------- ---- ---- ------------------
MKDEPTTS TS RW
MKEMPTS TS RW,RECP,CHKP
MKDEPTXB IX STOP
MKDEPTX1 IX RW
MKDEPTX2 IX RW
MKEMPXB IX RW,RBDP
MKEMPXB IX RW,RBDP
MKEMPX1 IX RW,RBDP
******* DISPLAY OF DATABASE CLAS01DB ENDED *******

Database Status Description
<
• Possible values for database status:
– RO The database is started for read activity only.
– RW The database is started for read and write activity.
– STOP The database is stopped.
– STOPP A stop is pending or is in progress for this DB.
– UT The database is started for utility processing only.

• Common values for space status:
– CHKP The object (a table space or a partition) is in check pending status.
– COPY The object (a table space or a partition) is in copy pending status.
– RBDP The object (an index space, index partition or logical index partition) is
in rebuild pending status. For logical partitions, status can also appear as
RBDP*.
– RECP The object (a table space, table space partition, index space, index
partition or logical index partition) is in recovery pending status.
– REORP The object (a table space or partition) is in recovery pending status.
– RO The table space, partition, index or index part is started for read-only.
– RW The table space, partition, index or index part is started for read write.
– STOP The table space, table space partition, index space or index space
partition is stopped.
– STOPP A stop is pending for the database, table space, table space
partition,index space or index space partition.
– UT The table space, table space partition, index space or index space
partition is started for utility processing only.
– UTRO A utility is in process on the table space, table space partition, index
space, or index space partition that allows only RO access.
– UTRW A utility is in process on the table space, table space partition, index
space or index space partition that allows RW access.
– UTUT A utility is in process on the table space, table space partition, index
space or index space partition that allows only UT access.

Stop Database

– Use to deallocate table/index spaces from the DB2 subsystem
– Required for certain types of ALTER DDL (MAXROWS)
– Required for certain “stand-alone” DB2

• Syntax
-STOP DATABASE(dbname)
SPACENAM(space-name)
• Example
-STO DB(CLAS01DB) SPACE(MKEMPTS)

Start Database

– Use to make database spaces available to DB2 subsystem
– Can start in any of the following modes
• RW - read/write (normal mode)
• RO - read-only
• UT - utility mode (no application access)
– Can apply to database or specific space

• Syntax
-START DATABASE(dbname)
SPACENAM(space-name)
ACCESS(mode)
• Example
-STA DB(CLAS01DB) SPACE(MKEMPTS)

Display Samples

-DIS DB(CLAS01DB) SPACE(*) RESTRICT LIMIT(*)
-DIS DB(CLAS01DB) SPACE(*) USE LIMIT(*)
-DIS DB(CLAS01DB) SPACE(*) ACTIVE USE LIMIT(*)
-DIS DB(CLAS01DB) SPACE(*) CLAIMERS LIMIT(*)
-DIS DB(CLAS01DB) SPACE(*) LOCKS LIMIT(*)
-DIS DB(CLAS01DB) SPACE(MK*)
-DIS DB(DSNDB06) SPACE(SYSDDF:SYSVIEWS)
-DIS DB(PWSE*) SPACE(*) RESTRICT LIMIT(*)

Display Utility

– Provides status of one or more DB2 utilities currently in progress
– Can use generic name for utility ID
e.g. ‘*’ for all utilities; ‘PRGI*’ for all utility ID’s that start with ‘PRGI’

• Syntax
-DISPLAY UTILITY(util-id)
• Example
-DIS UTIL(SADIMK*)

Term Utility

– Terminate one or more utilities
– Same generic name rules as those for DISPLAY UTILITY
– Be careful with generics: don’t kill some one else’s utility by mistake

• Syntax
-TERM UTILITY(util-id)
• Example
-TER UTIL(SADIMK1.RECVR)

Few Useful FAQs/Interview Questions for Mainframe Beginers :

2008-01-21T04:17:00.000-08:00

1. How to reconnect to Mainframes session when system says User already login ?
In Login Page check the Reconnect option with ‘S’ and then logon .. it will reconnect to the mainframe session

2. What is the basic use of directory blocks while creating pds ?
Directory blocks work like an index to the PDS and contain the member names of what is stored in the PDS. A directory block can hold approximately 6 member names so a PDS with 2 directory blocks can hold approximately 12 members and a PDS with 3 directory blocks can hold about 18 members

3.How to find How many members dose PDS has without opening it?
Open member in 3.4 optin and put ‘I’ to see the above information.

4.What is RESTART Parameter in JCL ?
RESTART Parameter is used to restart a job from specified/abended step manually. Below step shows how to start from step2 by skipping step1.

Eg: //Job1 Job Class=A, Restart = step2
//step1
:
//step2

5.How do you edit /resubmit a job which is available in spool ?
Use commands SJ or SE across (under column NP) the job in spool, and job will open in edit mode. Then sub the job. The changes made at this place are not reflected in actual job

6. How to open new session and swap between the screens ??
Use START command in command line to open new session or Put List Command line and use F9 Key .. u get a pop window ..select start new session option there

Use F9 key to swap between the session.

7. I have opend 10 screens. How to got to 9th screen ?
Put 9 on command line and press key F9 .. it will take u to 9 the screen. OR

Other way is to name your screen by some name.
a. Use the command screenname SA and enter
b. To get back to this screen, use SA in command line and press F9

8.Can a input/output file can have a record length of more than 80 ?
Yes, an input and output file can have file record length of more than or less than 80.
Only report file has a fixed length of 133 bytes of which 1 byte is used for line control by system

9.What is the length of Report ?
Record length of file should be 133 only, of which 132 bytes is used for data to be printed and one byte is used by printer for line control information

Eg : Example for Report defination
01 FD Report-rec
05 Rep-cntl PIc X(01) -- used by systen for line control info.
05 Rep-Rec PIC X(132).

10. What are Redifines ??
Redefines is used to share the same memory by different variable for reusability. This way we can redefine Numeic as Alphanumeric and vice versa... This is basic advantage of redefines.
Eg :
05 Ws-Alpha Pic X(40)
05 Ws-Num Redefine Ws-Alpha PIC 9(40)