FIRST MAX
SELECT MAX(SALARY)
FROM SALARY_TABLE
;
SECOND MAX
SELECT MAX(SALARY)
FROM SALARY_TABLE
WHERE SALARY < (SELECT MAX(SALARY)
FROM SALARY_TABLE)
;
N'TH MAX WITHOUT DUPLICATES
SELECT SALARY
FROM SALARY_TABLE A
WHERE 1 = ( SELECT COUNT(*)
FROM SALARY_TABLE B
WHERE B.SALARY >= A.SALARY)
;
-- N'TH MAX WITH DUPLICATES
SELECT DISTINCT SALARY
FROM SALARY A
WHERE 1 = ( SELECT COUNT(DISTINCT B.SALARY)
FROM SALARY_TABLE B
WHERE B.SALARY >= A.SALARY)
Sunday, July 12, 2009
DB2 -913 Abend
-913 UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE
reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME
resource-name
Explanation: The application was the victim in a deadlock or experienced
a timeout. The reason code indicates whether a deadlock or timeout
occurred.
Refer to message DSNT500I under "DSNT... Messages" in topic 3.14 for an
explanation of 'resource-type' and 'resource-name'. Refer to Table 3 in
Appendix B, "Problem determination" in topic APPENDIX1.2 for an
explanation of resource type codes.
System Action: The SQL statement cannot be executed. If the SQL statement
being executed was a cursor FETCH, DB2 closes the cursor.
SQLERRD(3) also contains the reason-code which indicates whether a
deadlock or timeout occurred. The most common reason codes are:
- 00C90088 - deadlock
- 00C9008E - timeout
Programmer Response: The application should either commit or roll back to
the previous COMMIT. Then, generally, the application should terminate.
See message DSNT376I for possible ways to avoid future deadlocks or
timeouts.
For more information about how CICS and TSO handle deadlocks, see Section
5 of Application Programming and SQL Guide.
Destination: 57033
-PROBLEM_DETERMI
No Time For Timeouts
The performance of a DB2 subsystem depends upon the interaction of many
different factors. However, there is an almost universal acknowledgment
that locking can and does have one of the more significant impacts.
It is important to remember that the job receiving the -911 SQLCODE
is most likely NOT the source of the problem. The source is usually the
job that continues to run. There are five factors to examine in resolving
the -911 conflict. These factors include:
* The lock size parameter that was specified at tablespace/index creation.
* The duration of the locks established by the BIND strategies.
The ACQUIRE and RELEASE strategies for the tablespaces and the
ISOLATION perameters for the page and subpage locks.
* The scope of the COMMIT in the application code.
* The manner in which the tablespace was started (RW or RO).
Most of the time, resource contention and timeouts are due to contention
on indexes, not data. The contention on indexes is something that is
relatively common.
Here are some suggestions to maximize concurrency and reduce the -911
problem:
* Create tables with LOCKSIZE=PAGE to improve concurrency and prevent
automatic lock escalation.
* Try to have the number of RIDs in a clustering index subpage equal to
the number of rows in a data page to limit the scope of a lock on a
leaf page.
* Set the subpage in the index equal to 16, to limit the scope of the
leaf page locking for TABLES WITH HEAVY UPDATE ACTIVITY to the index
columns.
* Start the table in RO for read only tables (rather than RW), and set
the locksize to PAGE. This will maintain flexibility while allowing
DB2 to override the BIND strategies and provide table locking.
* Limit the scope of SQL statements so that a statement locks 150 or
fewer pages on a table or indexspace for each unit of work that is
performed.
* Limit the scope of SQL statements so that the amount of work that is
performd can be completed before the MVS system swaps out the Allied
Address space due to the exaustion of service units as established
by the system programmers. If you're swapped out, you continue to hold
DB2 locks.
reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME
resource-name
Explanation: The application was the victim in a deadlock or experienced
a timeout. The reason code indicates whether a deadlock or timeout
occurred.
Refer to message DSNT500I under "DSNT... Messages" in topic 3.14 for an
explanation of 'resource-type' and 'resource-name'. Refer to Table 3 in
Appendix B, "Problem determination" in topic APPENDIX1.2 for an
explanation of resource type codes.
System Action: The SQL statement cannot be executed. If the SQL statement
being executed was a cursor FETCH, DB2 closes the cursor.
SQLERRD(3) also contains the reason-code which indicates whether a
deadlock or timeout occurred. The most common reason codes are:
- 00C90088 - deadlock
- 00C9008E - timeout
Programmer Response: The application should either commit or roll back to
the previous COMMIT. Then, generally, the application should terminate.
See message DSNT376I for possible ways to avoid future deadlocks or
timeouts.
For more information about how CICS and TSO handle deadlocks, see Section
5 of Application Programming and SQL Guide.
Destination: 57033
-PROBLEM_DETERMI
No Time For Timeouts
The performance of a DB2 subsystem depends upon the interaction of many
different factors. However, there is an almost universal acknowledgment
that locking can and does have one of the more significant impacts.
It is important to remember that the job receiving the -911 SQLCODE
is most likely NOT the source of the problem. The source is usually the
job that continues to run. There are five factors to examine in resolving
the -911 conflict. These factors include:
* The lock size parameter that was specified at tablespace/index creation.
* The duration of the locks established by the BIND strategies.
The ACQUIRE and RELEASE strategies for the tablespaces and the
ISOLATION perameters for the page and subpage locks.
* The scope of the COMMIT in the application code.
* The manner in which the tablespace was started (RW or RO).
Most of the time, resource contention and timeouts are due to contention
on indexes, not data. The contention on indexes is something that is
relatively common.
Here are some suggestions to maximize concurrency and reduce the -911
problem:
* Create tables with LOCKSIZE=PAGE to improve concurrency and prevent
automatic lock escalation.
* Try to have the number of RIDs in a clustering index subpage equal to
the number of rows in a data page to limit the scope of a lock on a
leaf page.
* Set the subpage in the index equal to 16, to limit the scope of the
leaf page locking for TABLES WITH HEAVY UPDATE ACTIVITY to the index
columns.
* Start the table in RO for read only tables (rather than RW), and set
the locksize to PAGE. This will maintain flexibility while allowing
DB2 to override the BIND strategies and provide table locking.
* Limit the scope of SQL statements so that a statement locks 150 or
fewer pages on a table or indexspace for each unit of work that is
performed.
* Limit the scope of SQL statements so that the amount of work that is
performd can be completed before the MVS system swaps out the Allied
Address space due to the exaustion of service units as established
by the system programmers. If you're swapped out, you continue to hold
DB2 locks.
DB2 -904 Abend
-904 UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON
reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME
resource-name
Explanation: The SQL statement could not be executed because resource
'resource-name' of type 'resource-type' was not available at the time for
the reason indicated by 'reason-code'.
System Action: The SQL statement cannot be executed. If the SQL statement
being executed was a cursor FETCH, DB2 closes the cursor. Subsequent
attempts to use that cursor without first doing an OPEN for it receive an
SQLCODE -501.
Programmer Response: Verify the identity of the resource that was not
available. To determine why the resource was unavailable, refer to the
specified 'reason-code'.
reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME
resource-name
Explanation: The SQL statement could not be executed because resource
'resource-name' of type 'resource-type' was not available at the time for
the reason indicated by 'reason-code'.
System Action: The SQL statement cannot be executed. If the SQL statement
being executed was a cursor FETCH, DB2 closes the cursor. Subsequent
attempts to use that cursor without first doing an OPEN for it receive an
SQLCODE -501.
Programmer Response: Verify the identity of the resource that was not
available. To determine why the resource was unavailable, refer to the
specified 'reason-code'.
More About Deadlock and Timeout
-911:
=====
-911 THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR
TIMEOUT. REASON reason-code, TYPE OF RESOURCE resource-type, AND
RESOURCE NAME resource-name
Explanation: The current unit of work was the victim in a deadlock, or
experienced a timeout, and had to be rolled back.
Note: The changes associated with the unit of work must be entered again.
SQLERRD(3) also contains the reason-code which indicates whether a
deadlock or timeout occurred. The most common reason codes are:
- 00C90088 - deadlock
- 00C9008E - timeout
System Action: The statement cannot be executed. The application is
rolled back to the previous COMMIT.
Programmer Response: A long-running application, or an application that
is likely to encounter a deadlock, should (if possible) issue frequent
COMMIT commands. This can lessen the possibility of a deadlock occurring.
See message DSNT376I for other possible ways to avoid future deadlocks or
timeouts. On receipt of the SQLCODE -911, the application should, in
general, terminate.
For more information about how IMS, CICS, and TSO handle deadlocks, see
Section 5 of Application Programming and SQL Guide.
00C9008E (Timeout Reason Code):
===============================
Explanation: A lock request for the resource identified by NAME could not
be granted, and the request waited for a period longer than the maximum
specified by the installation.
This reason code is issued by the following CSECT: DSNILMCL
System Action: The data manager function that detected this condition
returns 'resource not available' to its invoker.
Message DSNT378I might also be issued.
00C90088 (Deadlock Reason Code):
================================
Explanation: The resource identified by NAME in message DSNT501I is
involved in a deadlock condition.
This reason code is issued by the following CSECT: DSNILMCL
System Action: The requested operation is not performed.
User Response: Rerun the application.
System Programmer Response: If the deadlock condition becomes chronic,
examine the mix of applications running to determine why the deadlock is
occurring.
If statistics trace class 3 is active, IFCID 172 contains diagnostic
information. For a discussion of the effects of locking, see Section 5
(Volume 2) of Administration Guide . If you suspect and error in DB2,
refer to Section 3 of Diagnosis Guide and Reference for information on
identifying and reporting the problem.
=====
-911 THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR
TIMEOUT. REASON reason-code, TYPE OF RESOURCE resource-type, AND
RESOURCE NAME resource-name
Explanation: The current unit of work was the victim in a deadlock, or
experienced a timeout, and had to be rolled back.
Note: The changes associated with the unit of work must be entered again.
SQLERRD(3) also contains the reason-code which indicates whether a
deadlock or timeout occurred. The most common reason codes are:
- 00C90088 - deadlock
- 00C9008E - timeout
System Action: The statement cannot be executed. The application is
rolled back to the previous COMMIT.
Programmer Response: A long-running application, or an application that
is likely to encounter a deadlock, should (if possible) issue frequent
COMMIT commands. This can lessen the possibility of a deadlock occurring.
See message DSNT376I for other possible ways to avoid future deadlocks or
timeouts. On receipt of the SQLCODE -911, the application should, in
general, terminate.
For more information about how IMS, CICS, and TSO handle deadlocks, see
Section 5 of Application Programming and SQL Guide.
00C9008E (Timeout Reason Code):
===============================
Explanation: A lock request for the resource identified by NAME could not
be granted, and the request waited for a period longer than the maximum
specified by the installation.
This reason code is issued by the following CSECT: DSNILMCL
System Action: The data manager function that detected this condition
returns 'resource not available' to its invoker.
Message DSNT378I might also be issued.
00C90088 (Deadlock Reason Code):
================================
Explanation: The resource identified by NAME in message DSNT501I is
involved in a deadlock condition.
This reason code is issued by the following CSECT: DSNILMCL
System Action: The requested operation is not performed.
User Response: Rerun the application.
System Programmer Response: If the deadlock condition becomes chronic,
examine the mix of applications running to determine why the deadlock is
occurring.
If statistics trace class 3 is active, IFCID 172 contains diagnostic
information. For a discussion of the effects of locking, see Section 5
(Volume 2) of Administration Guide . If you suspect and error in DB2,
refer to Section 3 of Diagnosis Guide and Reference for information on
identifying and reporting the problem.
Saturday, July 11, 2009
Difference Between Static and Dynamic
The Difference :
The main difference between static and dynamic call is the time at which the load module of a subroutine gets invoked or gets attached to the load module of its main program. To get a better picture, let us take an example.
Let A be the Main Program, and this main program invokes the subroutines X, Y and Z. We would have coded the modules X, Y, Z and would have compiled it before executing A.
Static Call:
Now, a static call to the modules X, Y and Z means attaching the load modules of X, Y, Z to the load module of A. It’s like creating a single pack which contains all the load modules needed to execute a higher level program which calls many other programs. This can be done by specifying the compiler option "NODYAM" while compiling the program.
All the load modules of sub routines are embedded within the load module of the main progam.
Dynamic Call:
A dynamic call to the modules X,Y and Z means that you will not attach the load modules of X,Y, Z to the load module of A. The load modules of X,Y and Z will lie in a separate library and the load module of A will lie in a separate library. You will just refer to the library in which the load modules of X,Y and Z lie, while executing the program A. So when ever the subroutines X or Y or Z are invoked in the program A, the library which is specified will be searched for the load modules. By mistake, if the library of the subroutine does not contain the referred program, the system standard library will be searched. If the program is not found in standard library too, the programs will abend. This can be done by specifying the compiler option "DYNAM" while compiling the program.
Performance Comparison of Static Call and Dynamic Call:
Both Static call and Dynamic Call are advantageous depending on various parameters.
1) If your subroutines involve frequent changes, then static call will be a burden. Since the load module of the subroutine is embedded inside the load module of the main program, when ever you recompile the subroutine, you should relink(Process of replacing the old load module with new one) the main program. This surely will be a burden.
In dynamic call, it’s enough if you recompile the sub-routine and put it in the specified library. The new load module will be invoked automatically from the library specified.
2) On the other hand, statically called programs take less time to execute. The overload of searching the library and then invoking the required load is not present in case of static calls.
So, depending on our application need both static calls and dynamic calls are advantageous.
*****************************
Yes, we do have static and dynamic calls in cobol programming as in other programming languages.
Difference as per the syntax is, when we are calling a sub-program directly like
Call 'prog-x', its static, and when if we are assigning the sub-program name to local working storage variable and calling that sub-program through that assigned variable then its dynamic calling.
The main difference between static and dynamic call is the time at which the load module of a subroutine gets invoked or gets attached to the load module of its main program. To get a better picture, let us take an example.
Let A be the Main Program, and this main program invokes the subroutines X, Y and Z. We would have coded the modules X, Y, Z and would have compiled it before executing A.
Static Call:
Now, a static call to the modules X, Y and Z means attaching the load modules of X, Y, Z to the load module of A. It’s like creating a single pack which contains all the load modules needed to execute a higher level program which calls many other programs. This can be done by specifying the compiler option "NODYAM" while compiling the program.
All the load modules of sub routines are embedded within the load module of the main progam.
Dynamic Call:
A dynamic call to the modules X,Y and Z means that you will not attach the load modules of X,Y, Z to the load module of A. The load modules of X,Y and Z will lie in a separate library and the load module of A will lie in a separate library. You will just refer to the library in which the load modules of X,Y and Z lie, while executing the program A. So when ever the subroutines X or Y or Z are invoked in the program A, the library which is specified will be searched for the load modules. By mistake, if the library of the subroutine does not contain the referred program, the system standard library will be searched. If the program is not found in standard library too, the programs will abend. This can be done by specifying the compiler option "DYNAM" while compiling the program.
Performance Comparison of Static Call and Dynamic Call:
Both Static call and Dynamic Call are advantageous depending on various parameters.
1) If your subroutines involve frequent changes, then static call will be a burden. Since the load module of the subroutine is embedded inside the load module of the main program, when ever you recompile the subroutine, you should relink(Process of replacing the old load module with new one) the main program. This surely will be a burden.
In dynamic call, it’s enough if you recompile the sub-routine and put it in the specified library. The new load module will be invoked automatically from the library specified.
2) On the other hand, statically called programs take less time to execute. The overload of searching the library and then invoking the required load is not present in case of static calls.
So, depending on our application need both static calls and dynamic calls are advantageous.
*****************************
Yes, we do have static and dynamic calls in cobol programming as in other programming languages.
Difference as per the syntax is, when we are calling a sub-program directly like
Call 'prog-x', its static, and when if we are assigning the sub-program name to local working storage variable and calling that sub-program through that assigned variable then its dynamic calling.
SORT Tips
SORT
The SORT program is used to sort data into a certain sequence or to merge from 2 to 100 previously sorted input data sets into 1 output data set.
//STEP10 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SORTIN DD DISP=SHR,DSN=<< INPUT FILE NAME >>
//SORTOUT DD DISP=(MOD,CATLG,DELETE),SPACE=(CYL,(5,5),RLSE),
// UNIT=SYSDA,DCB=*.SORTIN,
// DSN=<< OUTPUT FILE NAME >>
//SYSIN DD *
//************** INCLUDE SORT STEPS HERE
1) TO SORT ON POSITIONS say for eg. 1 to 7
SORT FIELDS=(1,7,CH,A)
Where
Sort fields = (position ,length ,format ,sequence) or
Sort fields = (position ,length , sequence..),format = CH/BI/PD/ZD.d
PD=packed Decimal(COMP-3), ZD=zone decimal.
NOTE :-
Instead of using JCL to perform SORT operation , there's one simple alternative,
For eg:- Open a Flat file in edit mode. On the command line type (say) SORT 1,7 and press ENTER, the file will be sorted on positions 1 to 7 bytes.
2) TO COPY ALL THE RECORDS FROM INPUT FILE TO OUTPUT FILE
SORT FIELDS=COPY
3) TO COPY THOSE RECORDS WHICH SATISFY A PARTICULAR CONDITION.
INCLUDE COND=(38,10,CH,EQ,C'57071509',OR,36,10,CH,EQ,C' 57105779')
4) TO OMIT THOSE RECORDS WHICH SATISFY A PARTICULAR CONDITION.
OMIT COND=(19,1,CH,EQ,C'S',OR,19,1,CH,EQ,C'S')
5) TO SKIP CERTAIN NO OF RECORDS
SORT FIELDS=COPY,SKIPREC=1000
6) TO STOP AFTER COPYING CERTAIN NO OF RECORDS
SORT FIELDS=COPY,STOPAFT=5000
7) SKIPREC AND STOPREC CAN BE USED IN COMBINATION
SORT FIELDS=COPY,SKIPREC=1000,STOPAFT=5000
8) TO REMOVE DUPLICATES FROM THE FILE USING SORT
SORT FIELDS=(1,7,A),FORMAT=CH
SUM FIELDS=NONE
The SORT program is used to sort data into a certain sequence or to merge from 2 to 100 previously sorted input data sets into 1 output data set.
//STEP10 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SORTIN DD DISP=SHR,DSN=<< INPUT FILE NAME >>
//SORTOUT DD DISP=(MOD,CATLG,DELETE),SPACE=(CYL,(5,5),RLSE),
// UNIT=SYSDA,DCB=*.SORTIN,
// DSN=<< OUTPUT FILE NAME >>
//SYSIN DD *
//************** INCLUDE SORT STEPS HERE
1) TO SORT ON POSITIONS say for eg. 1 to 7
SORT FIELDS=(1,7,CH,A)
Where
Sort fields = (position ,length ,format ,sequence) or
Sort fields = (position ,length , sequence..),format = CH/BI/PD/ZD.d
PD=packed Decimal(COMP-3), ZD=zone decimal.
NOTE :-
Instead of using JCL to perform SORT operation , there's one simple alternative,
For eg:- Open a Flat file in edit mode. On the command line type (say) SORT 1,7 and press ENTER, the file will be sorted on positions 1 to 7 bytes.
2) TO COPY ALL THE RECORDS FROM INPUT FILE TO OUTPUT FILE
SORT FIELDS=COPY
3) TO COPY THOSE RECORDS WHICH SATISFY A PARTICULAR CONDITION.
INCLUDE COND=(38,10,CH,EQ,C'57071509',OR,36,10,CH,EQ,C' 57105779')
4) TO OMIT THOSE RECORDS WHICH SATISFY A PARTICULAR CONDITION.
OMIT COND=(19,1,CH,EQ,C'S',OR,19,1,CH,EQ,C'S')
5) TO SKIP CERTAIN NO OF RECORDS
SORT FIELDS=COPY,SKIPREC=1000
6) TO STOP AFTER COPYING CERTAIN NO OF RECORDS
SORT FIELDS=COPY,STOPAFT=5000
7) SKIPREC AND STOPREC CAN BE USED IN COMBINATION
SORT FIELDS=COPY,SKIPREC=1000,STOPAFT=5000
8) TO REMOVE DUPLICATES FROM THE FILE USING SORT
SORT FIELDS=(1,7,A),FORMAT=CH
SUM FIELDS=NONE
How TO Resolve SOC7 Abend
S0C7 Error the description comes in last line of run JCL SDSF
Press F11 (to scroll to right) to see the offset value clearly
COM1 is my program name
Take down the Offset value given in the message
Then go to the SDSF output of compile JCL
(Don’t forget to use XREF complier option at the time of compilation)
Program as per complier listing
Go to complier listing (i.e. the SDSF for compile JCL)
The offset is provided for each line with HEXLOC along with line number
(See the above slide)
Check with offset value is in which range
For example
The offset in our program is +000002E0
The line 14 (LINE # 000014) has the offset value of 0002D6 which indicates the starting value
As line 15 is a comment in my program it will not be displayed in the listing
The line 16 (LINE # 000016) has the offset value of 0002F0
The offset is in the range of starting offset of line 14 and line 16 offset clearly indicating line 14 has the error
The offset in our program is +000002E0 is less than the 0002F0 offset value of line 16 but Greater than 0002D6 line 14 offset value of so the error lies in line 14
The reason for the error is I used a UN initialized variable in arithmetic
Operation
A is not initialized,
And X is comp3; See the X is comp or comp3 as the error
may not come if X is Display.
Subscribe to:
Posts (Atom)