Wednesday, November 22, 2006

IDCAMS - skip

Skips 1st 4 records from INP01 and copies 5th and 6th records to OUT01

REPRO IFILE(INP01) OFILE(OUT01) SKIP(4) COUNT(2)

Friday, November 03, 2006

To find the version

To find the version of all the products installed on Z/OS
do the following:

In ISPF menu, type:
START TAS

Sunday, October 08, 2006

SQLCODE=-30080 / -904 - Conversation failure recovery

Problem: A VTAM APPC or TCP/IP conversation failed during or after allocation and is unavailable for use.

Symptom: VTAM or TCP/IP returns a resource unavailable condition along with the appropriate diagnostic reason code and message. A DSNL500 or DSNL511 (conversation failed) message is sent to the console for the first failure to a location for a specific logical unit (LU) mode or TCP/IP address. All other threads detecting a failure from that LU mode or IP address are suppressed until communications to that LU using that mode are successful.
DB2 returns messages DSNL501I and DSNL502I. Message DSNL501I usually means that the other subsystem is not up.

System action:
When the error is detected, it is reported by a console message and the application receives an SQL return code. For DB2 private protocol access, SQLCODE -904 (SQLSTATE '57011') is returned with resource type 1001, 1002, or 1003. The resource name in the SQLCA contains VTAM return codes such as RTNCD, FDBK2, RCPRI, and RCSEC, and any SNA SENSE information. See VTAM for MVS/ESA Messages and Codes for more information.

If you use application directed access or DRDA as the database protocols, SQLCODE -30080 is returned to the application. The SQLCA contains the VTAM diagnostic information, which contains only the RCPRI and RCSEC codes. For SNA communications errors, SQLCODE -30080 is returned. For TCP/IP connections, SQLCODE -30081 is returned. See DB2 Codesfor more information about those SQL return codes.

The application can choose to request rollback or commit. Commit or rollback processing deallocates all but the first conversation between the allied thread and the remote database access thread. A commit or rollback message is sent over this remaining conversation.
Errors during the conversation's deallocation process are reported through messages, but do not stop the commit or rollback processing. If the conversation used for the commit or roll back message fails, the error is reported. If the error occurred during a commit process, the commit process continues, provided the remote database access was read only; otherwise the commit process is rolled back.

System programmer action: The system programmer needs to review the VTAM or TCP/IP return codes and might need to discuss the problem with a communications expert. Many VTAM or TCP/IP errors, besides the error of an inactive remote LU or TCP/IP errors, require a person who has a knowledge of VTAM or TCP/IP and the network configuration to diagnose them.

Operator action: Correct the cause of the unavailable resource condition by taking action required by the diagnostic messages appearing on the console.

Tuesday, September 26, 2006

VERIFY in PL/1

Searches for first non-occurrence of any one of the elements of a string within another string.

VERIFYR:
Does same as VERIFY command, but the search starts from the right.

Example 1:
DCL MAINSTR CHAR(6) INIT ('A B ');
DCL SUBSTRG CHAR(1) INIT (' ');
DCL (POSN,RTN) FIXED BINARY(31,0);
RTN = VERIFY(MAINSTR, SUBSTRG, POSN);

When POSN=1, RTN = 1;
When POSN=2, RTN = 4;

Example 2:
DCL MAINSTR CHAR(6) INIT ('ANUK ');
DCL SUBSTRG CHAR(2) INIT ('NA');
RTN = VERIFY(MAINSTR, SUBSTRG);

Default search starts from 1st position.
RTN=3

Monday, August 28, 2006

System Abend: 0E37

PDS dataset filled all allocated directory space

Tuesday, August 01, 2006

System 806 Abend

Module not found in a library. This can occur if a new version of the module is put into a load library and is placed in secondary extents. To avoid this, when you allocate load libraries, specify a large primary allocation and 0 for the secondary allocation. This ensures that if there is not enough space, there will be an abend (for example, a B37 abend for insufficient space) when you link the module into the load library. Using this technique detects the space problem during the preparation step rather than at the run time

Friday, July 14, 2006

SQLCODE -927

The language interface was called but no connection had been made. Execute the program with the DSN command.

Friday, June 30, 2006

DB2: Fun with dates and times

To get current date, time and timestamp using SQL, reference the appropriate registers:

Select current date from sysibm.sysdummy1 ;
Select current time from sysibm.sysdummy1 ;
Select current timestampe from sysibm.sysdummy1 ;

Sysibm.Sysdummy1 is a special in-memory table that can be used to discover the value of DB2 registers as illustrated above.

Given a date, time, or timestamp, you can extract (where applicable) the year, month, day, hour, minutes, seconds, and microseconds portions independently using the appropriate function:

YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp)

Extracting the date and time independently from a timestamp is also very easy:

DATE (current timestamp)
TIME (current timestamp)

You can also perform date and time calculations using, for lack of a better term, English:

current date + 1 YEAR
current date + 3 YEARS + 2 MONTHS + 15 DAYS
current time + 5 HOURS - 3 MINUTES + 10 SECONDS

To calculate how many days there are between two dates, you can subtract dates as in the following:

days (current date) - days (date('1999-10-22'))

If you want to concatenate date or time values with other text, you need to convert the value into a character string first. To do this, you can simply use the CHAR() function:

char(current date)
char(current time)
char(current date + 12 hours)

To convert a character string to a date or time value, you can use:

TIMESTAMP ('2002-10-20-12.00.00.000000')
TIMESTAMP ('2002-10-20 12:00:00')
DATE ('2002-10-20')
DATE ('10/20/2002')
TIME ('12:00:00')

The TIMESTAMP(), DATE() and TIME() functions accept several more formats

Wednesday, June 21, 2006

DYNAMNBR parm

The DYNAMNBR parameter is used to indicate how many Task I/O Table (TIOT) slots to reserve for datasets that may be dynamically allocated during the job step. This parameter is equivalent to coding DD statements with the DYNAM keyowrd.

Syntax: DYNAMNBR = n

where "n" is a number in the range 0where D= no of DD stmts in the job step.

DYNAMNBR reserves space in the TIOT for the use of dynamically allocated datasets.

Tuesday, June 13, 2006

DB2 V8 Multiple DISTINCT clauses

Prior to Version 8..

SELECT DISTINCT C1, C2 FROM T1;
SELECT COUNT(DISTINCT C1) FROM T1;
SELECT C1, COUNT(DISTINCT C2) FROM T1 GROUP BY C1;
SELECT COUNT(DISTINCT(C1)), SUM(DISTINCT(C1)) FROM T1;

With Version 8..

SELECT DISTINCT COUNT(DISTINCT C1), SUM(DISTINCT C2) FROM T1;
SELECT COUNT(DISTINCT C1), AVG(DISTINCT C2) FROM T1 GROUP BY C1;
SELECT SUM(DISTINCT C1), COUNT(DISTINCT C1), AVG(DISTINCT C2) FROM T1 GROUP BY C1 HAVING SUM(DISTINCT C1)=1;

Not supported in Version 8..
SELECT COUNT(DISTINCT A1, A2) FROM T1 GROUP BY A2;
SELECT COUNT(DISTINCT(A1,A2)) FROM T1 GROUP BY A2;

Sunday, June 11, 2006

Quiesce tablespace

A QUIESCE flushes all DB2 buffers on the disk. This gives a correct snapshot of the database and should be used before and after any IMAGECOPY to maintain consistency.

Friday, June 09, 2006

NULLIF - DB2 function

The NULLIF function returns null if the two arguments are equal; otherwise, it returns the value of the first argument.
The two arguments must be compatible and comparable. Neither argument can be a BLOB, CLOB, DBCLOB, or distinct type. Character-string arguments are compatible and comparable with datetime values.

Ex:
Assume that host variables PROFIT, CASH, and LOSSES have decimal data types with the values of 4500.00, 500.00, and 5000.00 respectively. The following function returns a null value:

NULLIF (:PROFIT + :CASH , :LOSSES)

COALESCE - DB2 function

The COALESCE function returns the value of the first nonnull expression.

The arguments are evaluated in the order in which they are specified, and the result of the function is the first argument that is not null. The result can be null only if all arguments can be null. The result is null only if all arguments are null.

Ex:
Assume that SCORE1 and SCORE2 are SMALLINT columns in table GRADES, and that nulls are allowed in SCORE1 but not in SCORE2. Select all the rows in GRADES for which SCORE1 + SCORE2 > 100, assuming a value of 0 for SCORE1 when SCORE1 is null.

SELECT * FROM GRADES WHERE COALESCE(SCORE1,0) + SCORE2 > 100;

Thursday, June 08, 2006

CASE expressions in SQL

Ex: 01 : Assume that in the EMPLOYEE table the first character of a department number represents the division in the organization. Use a CASE expression to list the full name of the division to which each employee belongs.

SELECT EMPNO, LASTNAME,
CASE SUBSTR(WORKDEPT,1,1)
WHEN 'A' THEN 'Administration'
WHEN 'B' THEN 'Human Resources'
WHEN 'C' THEN 'Operations'
END
FROM EMPLOYEE ;

Ex:02 : You can also use a CASE expression to avoid "division by zero" errors. From the EMPLOYEE table, find all employees who earn more than 25 percent of their income from commission, but who are not fully paid on commission:

SELECT EMPNO, WORKDEPT, SALARY+COMM FROM EMPLOYEE
WHERE (CASE WHEN SALARY=0 THEN 0
ELSE COMM/(SALARY+COMM)
END) > 0.25;

Ex:03 : If the CASE expression is included in the SUM aggregate function, the CASE expression would prevent the errors. In the following query, the CASE expression screens out the unwanted division because the CASE operation is performed before the division.

SELECT REF_ID,PAYMT_PAST_DUE_CT,
SUM(CASE WHEN PAYMT_PAST_DUE_CT=0 THEN 0
WHEN PAYMT_PAST_DUE_CT>0 THEN
BAL_AMT/PAYMT_PAST_DUE_CT
END)
FROM PAY_TABLE
GROUP BY REF_ID,PAYMT_PAST_DUE_CT;

Ex:04 : This example shows how to group the results of a query by a CASE expression without having to re-type the expression. Using the sample employee table, find the maximum, minimum, and average salary. Instead of finding these values for each department, assume that you want to combine some departments into the same group.

SELECT CASE_DEPT,MAX(SALARY),MIN(SALARY),AVG(SALARY)
FROM (SELECT SALARY,
CASE WHEN WORKDEPT = 'A00' OR WORKDEPT = 'E21' THEN 'A00_E21'
WHEN WORKDEPT = 'D11' OR WORKDEPT = 'E11' THEN 'D11_E11'
ELSE WORKDEPT
END AS CASE_DEPT FROM DSN8810.EMP) X
GROUP BY CASE_DEPT;

Wednesday, June 07, 2006

Different ways for coding DCB parameter

DCB=*.ddname - While performing operations like SORT, REPRO, etc. :
Ex: The DCB parm for the output file for SORT can be coded as
DCB=*.SORTIN ( where SORTIN is the DD name for the input file for SORT)

DCB=*.stepname.ddname - Requests that the DCB parameter be copied from the DD statement "ddname" found in the same step "stepname"
Ex: DCB=*.STEP2.DD1

DCB=*.procexec.stepname.ddname - Requests that the DCB parameter be copied from DD statement "ddname" found in the previous step "stepname" found within a procedure "procexec" (name of EXEC statement invoking the procedure.)
Ex: DCB=*.PR1.STEP2.DD1

Sort - limit the records

To Skip certain number of records,
SORT FIELDS=COPY,SKIPREC=1000

To stop after copying certain number of records,
SORT FIELDS=COPY,STOPAFT=5000

Sorting without JCL

SORT FIELDS=(10,7,CH,A)

This option sorts the records based on the positions from 10th to 16th.

Instead of using JCL,
Open a flat file in edit mode, on the command line, type SORT 10,16 and press Enter.

Precompiler Option - STDSQL(NO/YES)

DB2 SQL and the SQL standard are not identical. The STDSQL precompiler option addresses some of the differences:
STDSQL(NO) indicates that conformance with the SQL standard is not intended. The default is the value of field STD SQL LANGUAGE on installation panel DSNTIP4 (which has a default of NO).

STDSQL(YES) indicates that conformance with the SQL standard is intended.
When a program is precompiled with the STDSQL(YES) option, the following rules apply:

#Declaring host variables:
All host variable declarations except in Java and REXX must lie between pairs of BEGIN DECLARE SECTION and END DECLARE SECTION statements:
BEGIN DECLARE SECTION

-- one or more host variable declarations

END DECLARE SECTION
Separate pairs of these statements can bracket separate sets of host variable declarations.

#Declarations for SQLCODE and SQLSTATE:
The programmer must declare host variables for either SQLCODE or SQLSTATE, or both. SQLCODE should be defined as a fullword integer and SQLSTATE should be defined as a 5-byte character string. SQLCODE and SQLSTATE cannot be part of any structure. The variables must be declared in the DECLARE SECTION of a program; however, SQLCODE can be declared outside of the DECLARE SECTION when no host variable is defined for SQLSTATE. For PL/I, an acceptable declaration can look like this:
DECLARE SQLCODE BIN FIXED(31);
DECLARE SQLSTATE CHAR(5);

In Fortran programs, the variable SQLCOD should be used for SQLCODE, and either SQLSTATE or SQLSTA can be used for SQLSTATE.

#Definitions for the SQLCA:
An SQLCA must not be defined in your program, either by coding its definition manually or by using the INCLUDE SQLCA statement. When STDSQL(YES) is specified, the DB2 precompiler automatically generates an SQLCA that includes the variable name SQLCADE instead of SQLCODE and SQLSTAT instead of SQLSTATE. After each SQL statement executes, DB2 assigns status information to SQLCODE and SQLSTATE, whose declarations are described above, as follows:

SQLCODE: DB2 assigns the value in SQLCADE to SQLCODE. In Fortran, SQLCAD and SQLCOD are used for SQLCADE and SQLCODE, respectively.
SQLSTATE: DB2 assigns the value in SQLSTAT to SQLSTATE. (In Fortran, SQLSTT and SQLSTA are used for SQLSTAT and SQLSTATE, respectively.)
No declaration for either SQLSTATE or SQLCODE: DB2 assigns the value in SQLCADE to SQLCODE.
If the precompiler encounters an INCLUDE SQLCA statement, it ignores the statement and issues a warning message. The precompiler also does not recognize hand-coded definitions, and a hand-coded definition creates a compile-time conflict with the precompiler-generated definition. A similar conflict arises if definitions of SQLCADE or SQLSTAT, other than the ones generated by the DB2 precompiler, appear in the program.

Defining SQL Communications Area in PL/I & COBOL

A PL/I program that contains SQL statements must include one or both of the following host variables:

PL/I:
*An SQLCODE variable, declared as BIN FIXED (31)
*An SQLSTATE variable, declared as CHARACTER(5)

COBOL:
*An SQLCODE variable declared as PIC S9(9) BINARY, PIC S9(9) COMP-4, PIC S9(9) COMP-5, or PICTURE S9(9) COMP
*An SQLSTATE variable declared as PICTURE X(5)


Alternatively, you can include an SQLCA, which contains the SQLCODE and SQLSTATE variables.

DB2 sets the SQLCODE and SQLSTATE values after each SQL statement executes. An application can check these values to determine whether the last SQL statement was successful. All SQL statements in the program must be within the scope of the declaration of the SQLCODE and SQLSTATE variables.
Whether you define the SQLCODE or SQLSTATE variable or an SQLCA in your program depends on whether you specify the precompiler option STDSQL(YES) to conform to SQL standard, or STDSQL(NO) to conform to DB2 rules.

If you specify STDSQL(YES):
When you use the precompiler option STDSQL(YES), do not define an SQLCA. If you do, DB2 ignores your SQLCA, and your SQLCA definition causes compile-time errors.
If you declare an SQLSTATE variable, it must not be an element of a structure. You must declare the host variables SQLCODE and SQLSTATE within the BEGIN DECLARE SECTION and END DECLARE SECTION statements in your program declarations.

If you specify STDSQL(NO):
When you use the precompiler option STDSQL(NO), include an SQLCA explicitly. You can code the SQLCA in a PL/I program, either directly or by using the SQL INCLUDE statement. The SQL INCLUDE statement requests a standard SQLCA declaration:
EXEC SQL INCLUDE SQLCA;

ONCODE: PL/I Built-In function

It provides a fixed-point binary value that depends on the cause of the last condition. If it is used out of context, zero is returned.

Indicator variables

An indicator variable is a 2-byte integer (short int). An indicator variable array is an array of 2-byte integers (short int). You use indicator variables and indicator variable arrays in similar ways.

Using indicator variables: If you provide an indicator variable for the variable X, when DB2 retrieves a null value for X, it puts a negative value in the indicator variable and does not update X. Your program should check the indicator variable before using X. If the indicator variable is negative, you know that X is null and any value you find in X is irrelevant.

When your program uses X to assign a null value to a column, the program should set the indicator variable to a negative number. DB2 then assigns a null value to the column and ignores any value in X.

Example:

/* Declaration*/
EXEC SQL BEGIN DECLARE SECTION;
char ClsCd[8];
char Bgn[9];
char End[9];
short Day, DayInd, BgnInd, EndInd;
EXEC SQL END DECLARE SECTION;


EXEC SQL FETCH CLS_CURSOR INTO :ClsCd,
:Day :DayInd,
:Bgn :BgnInd,
:End :EndInd;

DSNTIAR in PL/I

Syntax: CALL DSNTIAR ( sqlca, message, lrecl );

Ex:
DCL DATA_LEN FIXED BIN(31) INIT(132);
DCL DATA_DIM FIXED BIN(31) INIT(10);
DCL 1 ERROR_MESSAGE AUTOMATIC,
3 ERROR_LEN FIXED BIN(15) UNAL INIT((DATA_LEN*DATA_DIM)),
3 ERROR_TEXT(DATA_DIM) CHAR(DATA_LEN);
···
CALL DSNTIAR ( SQLCA, ERROR_MESSAGE, DATA_LEN );


where ERROR_MESSAGE is the name of the message output area, DATA_DIM is the number of lines in the message output area, and DATA_LEN is the length of each line.

Because DSNTIAR is an assembler language program, you must include the following directives in your PL/I application:

DCL DSNTIAR ENTRY OPTIONS (ASM,INTER,RETCODE);

Handling SQL Error return codes - DSNTIAR

DSNTIAR is a subroutine used to convert an SQL return code into a text message. DSNTIAR takes data from the SQLCA, formats it into a message and places the result in the message output area, provided in the application program.

Syntax:
rc = DSNTIAR (&sqlca, &message, &lrecl)
&sqlca : An SQL communication Area
&message : An output area in VARCHAR format, in which DSNTIAR places the message text. The first halfword contains the length of the remaining area; its minimum value is 240
The output lines of text, each line being the length specified in &lrecl, are put in this area.

Monday, June 05, 2006

wanna know MVS version?

Open SDSF session and enter WHO. In reply to this, several information along with the versions of MVS, JES2, ISPF, SDSF etc..

Sunday, June 04, 2006

Easy way to search a member in multiple PDS

Go to 3.4 and display all the PDS you want to search for.

In the command line, give M <member name>

This command will not work for migrated PDS.

Now the message "MEMBER(S): memname found" will apppear in the right side of the panel.

Saturday, June 03, 2006

JOBLIB, STEPLIB, JCLLIB and PROCLIB

JOBLIB: is used at the start of the JCL to specify and override library to use for the entire job. It is placed immediately after the job statement and applies to the whole job.
STEPLIB: is used in an individual step in the job. It is placed immediately after the EXEC statement to which it applies to and is used only for that step.
JCLLIB & PROCLIB: are used to specify where JCL is to be loaded from rather than programs. JCLLIB is used with the INCLUDE MEMBER statement to specify the libraries where pieces of JCL are held that can be included into a number of jobs. PROCLIB is the DD statement to override where to get procedures from.

DFSORT FAQ _link

http://www.damos.dircon.co.uk/html/dfsort_faq.html

Can I reformat a record layout before/after sorting?

INREC reformats the record layout before it is passed through the sort, while OUTREC will reformat the record layout after the record has been sorted

SORT FIELDS=(1,10,CH,A)
INREC=(1,10,CH,50,4,PD,40,4,PD)

SORT FIELDS=(1,10,CH,A)
OUTREC=(1,10,50,4,40,4)

IEFBR16

IEFBR16 and IEFBR14 have the same functionality. The only difference is the time taken by the utils. IEFBR14 finishes in no time, whereas IEFBR16 takes 2 minutes.

GDG - Absolute Generation and Version Numbers

An absolute generation and version number is used to identify a specific generation of a GDG. The generation and version numbers are in the form GxxxxVyy, where xxxx is an unsigned 4-digit decimal generation number (0001 through 9999) and yy is an unsigned 2-digit decimal version number (00 through 99). For example:
A.B.C.G0001V00 is generation data set 1, version 0, in generation data group A.B.C.
A.B.C.G0009V01 is generation data set 9, version 1, in generation data group A.B.C.
The number of generations and versions is limited by the number of digits in the absolute generation name; that is, there can be 9,999 generations. Each generation can have 100 versions. The system automatically maintains the generation number. The number of generations kept depends on the size of the generation index. For example, if the size of the generation index permits ten entries, the ten latest generations can be maintained in the GDG.
The version number lets you perform normal data set operations without disrupting the management of the GDG. For example, if you want to update the second generation in a 3-generation group, replace generation 2, version 0, with generation 2, version 1. Only one version is kept for each generation.
You can catalog a generation using either absolute or relative numbers. When a generation is cataloged, a generation and version number is placed as a low-level entry in the GDG. To catalog a version number other than V00, you must use an absolute generation and version number.
You can catalog a new version of a specific generation automatically by specifying the old generation number along with a new version number. For example, if generation A.B.C.G0005V00 is cataloged and you now create and catalog A.B.C.G0005V01, the new entry is cataloged in the location previously occupied by A.B.C.G0005V00. The old entry is removed from the catalog, to make room for the newer version, and may or may not be scratched depending on what limit processing options are specified for the GDG base. For system-managed data sets, if scratch is specified, the older version is scratched from the volume. If noscratch is specified, or if the attempt to scratch the DSCB fails, the older version is not scratched and the generation data sets is recataloged as a non-VSAM data set with the GnnnnVnn name not associated with the GDG base. For non-system-managed data sets, the older version is also governed by the GDG base limit processing options. If noscratch is specified for the base, the older GDS version is not scratched. To scratch the old version and make its space available for reallocation, include a DD statement, describing the data set to be deleted, with DISP=(OLD,DELETE) when the data set is to be replaced by the new version.

Can LOCK TABLE used on a view?

No. To lock a view, take lock on the underlying tables.

Friday, June 02, 2006

Abend B37

The most common reason of a B37 abend is a situation when operating system can't allocate secondary extenet for DASD output dataset because there is not enough space on the DASD unit(s), defined for the dataset allocation.
Sequential dataset can be allocated in up to 16 extents (normally one primary and 15 secondary). When OS can't find space for primary allocation, job gets cancelled with JCL error before attempting to execute it. If primary allocation request is satisfied, job starts running, allocating secondary extents when (and if ) necessary. If an attempt to allocate secondary extent fails, job abends with B37.

just an intro

This is has been created to help people who are looking for jobs in Mainframes /PL/I / DB2. Certainly not a Job pool.. but a techie pool..
Going to be filled with FAQs in Mainframes/PLI / DB2

Good Luck.. Celebrate learning!!