APAR status
Closed as canceled.
Error description
5740xyr00 DB2 R410 V4 This APAR documents changes to the DB2 Application Programming & SQL Guide SC26326600 which did not make Version 4.1 GA pubs This APAR is continued in II09144, II09590 & II11727. ================================================================ Version 4 Book Title: Application Programming and SQL Guide Pages: 2-53 Change the following query: SELECT EMPNO,LASTNAME,DEPTNAME,PROJNO FROM DSN8410.EMP INNER JOIN DSN8410.DEPT ON WORKDEPT = DEPTNO LEFT OUTER JOIN DSN8410.PROJ ON EMPNO = RESPEMP WHERE LASTNAME > 'S'; to this query: SELECT EMPNO,LASTNAME,DEPTNAME,PROJNO FROM DSN8410.EMP INNER JOIN DSN8410.DEPT ON WORKDEPT = DSN8410.DEPT.DEPTNO LEFT OUTER JOIN DSN8410.PROJ ON EMPNO = RESPEMP WHERE LASTNAME > 'S'; ============================================================ Version 4 Book Title: Applicaition Programming and SQL Guide Pages: 4-13 Under Major Changes in Version 4 of DB2 for MVS/ESA, change the sixth bullet to this: - A change in the default for the bind option CURRENTDATA, from NO to YES, and the ability to avoid acquiring locks for ambiguous cursors bound with CURRENTDATA(NO). If you used a BIND command to bind your plans or packages without specifying the CURRENTDATA option, and if you use a BIND command after you migrate to Version 4, you must specify CURRENTDATA(NO) to keep the same locking behavior. If you use a REBIND command after migration, the previously used CURRENTDATA option is used, and the locking behavior remains the same. Therefore, if you did not specify any CURRENTDATA option, the plan or package continues to use CURRENTDATA(NO) after you migrate and rebind. (The exception is for plans that were last bound at Version 2 Release 2 or earlier releases. If you bind or rebind those plans after migrating to Version 4 without specifying the CURRENTDATA option, CURRENTDATA(YES) is used.) ============================================================ Version 4 Book Title: Application Programming and SQL Guide Pages: 4-17 Change the contents of the IMS label box under "Indications of Deadlocks" to this text: If you are using IMS, and a deadlock or timeout occurs, the following actions take place: * In a DL/I batch application, the application process abnormally terminates with a completion code of 04E and a reason code of 00D44033 or 00D44050. * In any IMS environment except DL/I batch: * DB2 performs a rollback operation on behalf of your application process to undo all DB2 updates that occurred during the current unit of work. * For a non-message driven BMP, IMS issues a rollback operation on behalf of your application. If this operation is successful, IMS returns control to your application, and the application receives SQLCODE -911. If the operation is unsuccessful, IMS issues user abend code 0777, and the application does not receive an SQLCODE. * For an MPP, IFP, or message driven BMP, IMS issues user abend code 0777, rolls back all uncommitted changes, and reschedules the transaction. The application does not receive an SQLCODE. ============================================================= Version 4 Book Title: Application Programming and SQL Guide Pages: 4-26 Modify the description of the ACQUIRE and RELEASE bind parameters to indicate that these options are not for row or page locks. The ACQUIRE and RELEASE options of bind operations determine when DB2 locks a table or table space your application uses and when it releases the lock. |(The ACQUIRE and RELEASE options do not affect row or page |locks.) The options apply to static SQL statements, which are bound before your program executes. If your program executes dynamic SQL statements, the objects they lock are locked when first accessed and released at the next commit point. ============================================================ Version 4 Book Title: Application Programming and SQL Guide Pages: 4-30 Clarify the cases where a -510 can occur re: PN87414 Problems with ambiguous cursors: Ambiguous cursors can sometimes prevent DB2 from using lock avoidance techniques. Another possible problem from using ambiguous cursors occurs when your program has an ambiguous cursor and performs the following operations, which can cause your program to receive a -510 SQLCODE: -The plan or package is bound with CURRENTDATA(NO) -An OPEN CURSOR statement is performed before a dynamic DELETE WHERE CURRENT OF statement against that cursor is prepared -One of the following conditions is true for the open cursor: -Lock avoidance is successfully used on that statement. -Query parallelism is used. -The cursor is distributed, and block fetching is used. In all cases, it is a good programming technique to eliminate the ambiguity by declaring the cursor with one of the clauses FOR FETCH ONLY or FOR UPDATE OF. ============================================================ Version 4 Book Title: Application Programming and SQL Guide Pages: 6-49 In SYSPROCEDURES Columns that Uniquely Identify a Procedure, under AUTHID, add this line: The ID you specify must be a primary authorization ID. ============================================================ Version 4 Book Title: Application Programming and SQL GUide Pages: 6-73 Add this example under Examples of Predicate Properties: - WHERE T1.COL1=T2.COL1 AND T3.COL2=T4.COL2 Assume that T1.COL1 and T2.COL1 have the same data types, and T3.COL2 and T4.COL2 have the same data types. If T1.COL1 and T2.COL1 have different nullability attributes, but T3.COL2 and T4.COL2 have the same nullability attributes, and DB2 chooses a merge scan join to evaluate the compound predicate, the compound predicate is stage 1. However, if T3.COL2 and T4.COL2 also have different nullability attributes, and DB2 chooses a merge scan join, the compound predicate is not stage 1. ============================================================== Version 4 Book Title: Application Programming and SQL Guide Pages: 6-122 Change Description: When Views or Nested Table Expressions are Materialized In general, DB2 uses materialization to satisfy a reference to a view or nested table expression when there is aggregate processing (grouping, column functions, distinct), indicated by the defining subselect, in conjunction with either aggregate processing indicated by the statement referencing the view or nested table expression, or by the view or nested table expression participating in a join. Table 38 indicates some cases in which materialization occurs. DB2 can also use materialization in statements that contain multiple outer joins, or outer joins combined with inner joins. Cases when DB2 Performs View or Table Expression Materialization. The "X" indicates a case of materialization. Notes follow the table. VIEW DEFINITION OR NESTED TABLE EXPRESSION A SELECT FROM USES...(2) A VIEW OR A NESTED TABLE GROUP BY DISTINCT Column Column Outer EXPRESSION function function join USES...(1) DISTINCT ----------------------------------------------------------- Inner join X X X X X ----------------------------------------------------------- Outer join (3) X X X X X ----------------------------------------------------------- GROUP BY X X X X X ----------------------------------------------------------- DISTINCT - X - X - ----------------------------------------------------------- Column function X X X X X ----------------------------------------------------------- Column function X X X X X DISTINCT ----------------------------------------------------------- SELECT subset of - X - - - view or nested table expression columns NOTES TO TABLE 38: 1.If the view is referenced as the target of an INSERT, UPDATE, or DELETE, then view merge is used to satisfy the view reference. Only updatable views can be the target in these statements. See Chapter 6 of SQL Reference for information on which views are read-only (not updatable). An SQL statement can reference a particular view multiple times where some of the references can be merged and some must be materialized. 2.If a SELECT list contains a host variable in a nested table expression, then materialization occurs. For example: SELECT C1 FROM (SELECT :HV1 AS C1 FROM T1) X; 3. Additional details about materialization with outer joins: o If a WHERE clause exists in a view or nested table expression, and it does not contain a column, materialization occurs. For example: SELECT X.C1 FROM (SELECT C1 FROM T1 WHERE 1=1) X LEFT JOIN T2 Y ON X.C1=Y.C1; o If the outer join is a full outer join and the SELECT list of the view or nested table expression does not contain a standalone column for the column that is used in the outer join ON clause, then materialization occurs. For example: SELECT X.C1 FROM (SELECT C1+10 AS C2 FROM T1) X FULL JOIN T2 Y ON X.C2=Y.C2; o If there is no column in a SELECT list of a view or nested table expression, materialization occurs. For example: SELECT X.C1 FROM (SELECT 1+2+:HV1. AS C1 FROM T1) X LEFT JOIN T2 Y ON X.C1=Y.C1; o Most cases of nested outer join statements cause views and nested table expressions to be materialized. o If the result of an outer join undergoes another join of any type, the result of the first outer join is materialized before the next join begins. o If the result of an inner join undergoes a further outer join, the result of the first inner join is materialized before the next join begins. ============================================================ Version 4 Book Title: Application Programming and SQL Guide Pages: 6-129 Remove the following items from the table of parallelism restrictions: - Correlated subquery This item causes confusion. The outer query does spawn parallel tasks, each of which runs on a range of the inner table. - Updatable or ambiguous cursor with CURRENTDATA(YES) Move to the 'Turning on Parallel Processing' section on 6-127. - Unions across query blocks Another misleading and confusing item. Each query in the union is running in parallel; therefore, this item should not be in the table. ============================================================= Version 4 Book Title: Application Programming and SQL Guide Pages: 6-130 Change Description: The example for hybrid join should be: T1 0 3 1 null null null null T2 2 6 2 3 3 1 2 ============================================================ Version 4 Book Title: Application Programming and SQL Guide Pages: 6-161 Change Description: In Table 45, Examples of CAF TRANSLATE Calls, change the the line for C and C++ from: fnret=dsnali(&connfn[0],&ssid[0], &retcode,&reascode); to: fnret=dsnali(&connfn[0],&sqlca, &retcode,&reascode); ============================================================ Version 4 Book Title: Application Programming and SQL Guide Pages: 6-164 Change the definition of EUT from "extended unlock task" to "enabled unlocked task." ============================================================ This APAR is continued in II11727.
Local fix
Problem summary
Problem conclusion
Temporary fix
Comments
close for INTERNET viewing
APAR Information
APAR number
II10882
Reported component name
PB LIB INFO ITE
Reported component ID
INFOPBLIB
Reported release
001
Status
CLOSED CAN
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
1997-10-22
Closed date
1997-11-01
Last modified date
1999-06-08
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Applicable component levels
[{"Business Unit":{"code":null,"label":null},"Product":{"code":"SG19O","label":"APARs - MVS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"001","Edition":"","Line of Business":{"code":"","label":""}},{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"001","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
08 June 1999