Page tree
Skip to end of metadata
Go to start of metadata

On this page:

Target audience:

CAST Administrators

Summary: this page provides detailed information about CAST's support for the IBM DB2 technology.
CAST is continually striving to make its software better suited to customer needs. As part of this effort, we have been working on removing the dependency in our DB2 analyzer for access to a live instance of a database to perform an analysis. This current method of DB2 analysis is being phased out and will be replaced by a new offline analysis process offered by the SQL Analyzer extension. This extension is already available and ready for use in the CAST AIP 8.x product line. The extension provides a way to directly analyze DB2 DDL files – please see http://doc.castsoftware.com/display/DOCEXT/SQL+Analyzer+-+2.0 for more information. The current version of the DB2 analyzer in CAST AIP 8.2.x is still fully functional, supported and will continue to operate as indicated below. The transition to the new analysis approach will be completed when the current version of the DB2 analyzer is phased out in the upcoming CAST AIP 8.3 release.

Detailed technology support

SQL-PSM for z/OS and OS/390

Language Version

SupportedSupported by referenceDeprecated support

Comments

SQL-PSM DB2/390 (Mainframe) V6

 (tick) 

 

SQL-PSM Universal Database for z/OS and OS/390 (Mainframe) V7

 (tick) 

 

SQL-PSM Universal Database for z/OS (Mainframe) V8

 (tick) 

 

SQL-PSM Universal Database for z/OS (Mainframe) V9

 (tick) 

XQuery and XPath are not taken into account.

SQL-PSM Universal Database for z/OS (Mainframe) V10(tick)  
SQL-PSM Universal Database for z/OS (Mainframe) V11(tick)  

Notes:

  • For IBM DB2 OS/390: the database must be replicated on a DB2 UDB (Unix, Linux, Windows) server and then packaged and analyzed in the same way as a DB2 UDB server (see below).
  • Stored procedures, functions and triggers are taken into account but their content is not analyzed. Only schemas, tables, views and types are saved with their links.

SQL-PSM for DB2 UDB (Universal Database) for Linux, Unix, Windows (LUW)

Language Version

SupportedSupported by referenceDeprecated support

Comments

SQL-PSM Universal Database V7.1 (fixpack ≥ 3) & V7.2

 (tick) 

 

SQL-PSM Universal Database V8.2

 (tick) 

 

SQL-PSM Universal Database V9.1.x

 (tick) 

XQuery and XPath are not taken into account.

 

SQL-PSM Universal Database V9.5

(tick)  

SQL-PSM Universal Database V9.7

(tick)  
SQL-PSM Universal Database V10.1(tick)  
SQL-PSM Universal Database V10.5 (tick) 

Required third-party software

To successfully deliver and analyze IBM DB2 code, the following third-party software is required:

Install on workstation running the DMT (for extraction)
Install on workstation running CMS (for analysis)

IBM DB2 UDB (and any databases replicated from IBM DB2 OS/390)

All that is required is network access to the live database/schema to be packaged.

IBM DB2 z/OS

A specific standalone extractor is delivered by CAST (see CAST DB2 zOS Extractor Admin Guide) and this must be installed and run on the z/OS instance BEFORE the packaging is actioned in the CAST Delivery Manager Tool. This standalone extractor outputs information from the DB2 system catalog into flat files: these files must then be transferred onto a Windows workstation for packaging via the CAST Delivery Tool Manager.

IBM DB2 UDB

A client connection layer or driver must be installed on the workstation that will be used to run the CAST Management Studio - i.e the machine on which the analysis will be executed

CAST Supports the IBM DB2 UDB 9.1, 10.1 and 10.5 connection layers, however, CAST recommends installing the most recently released and supported (by CAST) client connection layer: IBM DB2 UDB client connection layers are backwards compatible, therefore and for example, you can connect to and analyze schemas hosted on an IBM DB2 UDB 9.1 server when using a DB2 UDB 10.5 client connection layer.

In addition, you must create a 64-bit ODBC User DSN for each DB2 UDB server you want to analyze.

Network access to the live database/schema to be analyzed is required.

IBM DB2 z/OS

Nothing to install

Packaging and analyzing SQL-PSM for DB2 UDB

Because of the way in which the CAST DB2 analyzer is built, the packaging and analysis mechanisms differ from other CAST "server side" analyzers (such as for Oracle Server, Microsoft SQL Server and Sybase ASE). Please read the following information about how to handle the analysis of SQL-PSM for DB2 UDB:

DescriptionPage

How to package an SQL-PSM for DB2 UDB schema using the CAST Delivery Manager Tool

How do I create a package for an application database
Rights required to package and analyze SQL-PSM for DB2 UDBRequired RDBMS rights for packaging a database

How to:

  • validate the CAST Delivery Manager Tool delivery in the CAST Management Studio
  • check delivered connection parameters
  • configure a 64-bit ODBC System or User DSN entry

Validate and configure RDBMS connection for DB2 UDB Analysis Units

DB2 objects and links

The following section lists the objects and links between objects that the DB2 analyzer is capable of detecting and storing in the CAST Analysis Service, whether for DB2 z/OS or DB2 UDB:

Objects

Database

Schema

Alias 

Distinct UDT

Function

Index

Nickname

Procedure

Structured UDT

Table

Trigger

View

Link Type When is this type of link created ?
CALL

When:

  • a trigger, view, function, or procedure calls a function.
  • a procedure calls another procedure.

DDL
  • Create
  • Drop

DDL Create example:

CREATE PROCEDURE SP_CreateTable_Table0001
()
MODIFIES SQL DATA
NOT DETERMINISTIC
CALLED ON NULL INPUT
LANGUAGE SQL

P1: BEGIN

Declare Error int default 0;
CREATE Table Table0001 ( ID CHARACTER(4), Qty INTEGER);
return Error;

END P1

DDL Drop example:

CREATE PROCEDURE SP_DDL_DropTable_Table0001
()
MODIFIES SQL DATA
NOT DETERMINISTIC
CALLED ON NULL INPUT
LANGUAGE SQL

P1: BEGIN

Declare Error int default 0;
DROP Table Table0001;
return Error;

END P1
FIRE
  • Insert
  • Update
  • Delete
  • Select
  • Before
  • After
  • For Each Row
  • For All Row
When a table or view fires a trigger before or after the table or view is modified (for each row / for all rows).
INHERIT

When:

    • a structured type inherits fields and methods from another structured type.
    • a table inherits columns from another table.
    • a view inherits columns from another view.
LOCKWhen a procedure contains a LOCK type instruction on a table. The "lock" on the table is removed by a new LOCK, a COMMIT or a ROLLBACK.
PROTOTYPEWhen an alias is the prototype of a table or view.
REFERWhen a referential "delete" or "update" link exists between two tables.
RELY ON

When:

    • an index organizes data in a table
    • a table is based on a structured type
    • a column in a table, view, alias or nickname is characterized by a distinct or structured UDT
    • a column in a table, view, alias or nickname is a reference on a structured UDT.
    • a parameter in a function or procedure is characterized by distinct or structured UDT.
    • a trigger, procedure, view or function handles a distinct or structured UDT.
USE
  • Insert
  • Update
  • Delete
  • Select

When:

  • a trigger or a procedure contains a DML INSERT, UPDATE, DELETE, or SELECT INTO instruction that handles a table, a view an alias or a nickname.
  • a table is based on a structured type and is linked to a hierarchical table.

Technology support notes

This section provides more detail about the support for specific IBM DB2 syntax and the way in which CAST handles them.

DB2 UDB v. 9.5

  • DECFLOAT (new type)
  • BOOLEAN (new type)
  • identifier '[' expression ']' - an expression used to access a table element
  • SET statement syntaxes - for new table types
  • Support for new built-in functions and predicates:
    • BITAND
    • BITANDNOT
    • BITOR
    • BITXOR
    • BITNOT
    • NVL
    • LEAST
    • GREATEST
    • DECODE
    • COMPARE_DECFLOAT
    • DECFLOAT
    • NORMALIZE_DECFLOAT
    • QUANTIZE
    • TOTALORDER
    • ARRAY_AGG
    • ARRAY_EXISTS
  • CURRENT SESSION_USER and CURRENT SYSTEM_USER - new special registers

DB2 UDB v. 9.7

  • CURSOR - new type
  • CAST '(' expression AS CURSOR ')' - new CAST specification
  • anchored type - when specifying a variable, it is possible to specify an anchored type
  • AS LOCATOR clause: this clause can occur in the following locations:
    • CREATE FUNCTION (in RETURNS, RETURNS TABLE and RETURNS ROW clauses)
    • in function and procedure parameters
    • CREATE METHOD (in RETURNS clauses)
  • IS OPEN and IS FOUND - new cursor predicates
  • DEFAULT clause - for function and procedure parameters
  • IN, OUT and INOUT - new parameters
  • CREATE INDEX (PARTITIONED or NOT PARTITIONED) - new partitioning clauses
  • COMPRESS clause - for CREATE INDEX and CREATE TABLE
  • OR REPLACE clause - new clause (after a CREATE) for procedures, triggers and views
  • OPEN CURSOR statement syntaxes - for cursor variables
  • DECLARE variable statement syntaxes - DEFAULT and CONSTANT clauses
  • DECLARE cursor statement syntaxes - for the following new clauses:
    • WITHOUT HOLD
    • WITHOUT RETURN
    • ASENSITIVE
    • SENSITIVE
  • DISTRIBUTE BY clause - in DECLARE GLOBAL TEMPORARY TABLE
  • EXECUTE statement - new INTO and INTO DESCRIPTOR clauses

Notes

The following section lists technical and functional aspects with regard to analysis of DB2 source code:

Specific to DB2 for z/OS

  • When analyzing z/OS objects, only those dependent objects will be light-imported that can be identified through a Grep operation.
  • The DB2 Analyzer for z/OS is not grammar-based and as such the analyzer will not output any syntax errors even if any exist.
  • Not all CAST diagnostics are computed for DB2 z/OS objects. The following are not computed:
    • Number of code lines
    • Number of leading comments
    • Number of comments and code lines
  • The following CAST diagnostics are computed using the Metrics Assistant only. In addition, these computations are applied only to views and triggers:
    • FromClause
    • GroupBy
    • JoinMore
    • NotExits
    • NotIn
    • SelectColumn
    • Select All
    • SubQueries
    • UnionVsUnionAll
    • WhereClause
    • Raw SQL Complexity
  • Neither procedure parameters nor function parameters are stored in the CAST Analysis Service
  • Stored procedures, functions and triggers are extracted and taken into account by the analyzer but their content is not analyzed.
  • Materialized Query Tables, Auxiliary Tables and Created Global Temp Tables are not taken into account by DB2 Analyzer for z/OS.

Specific to DB2 UDB

  • In DB2 v9, XQuery and XPath are not currently taken into account.

General

Resolution error

When analyzing code similar to the following, where a stored procedure (in this case GETALLSALES) exists in both the current schema (PUBS) and the schema in the current path (CASTPUBS):

SET CURRENT SCHEMA = CASTPUBS
SET CURRENT PATH = current path, 'CASTPUBS'
CREATE PROCEDURE PUBS.SP_RESOLVEQUALIFIER ( )
P1: BEGIN
CALL GETALLSALES();
END P1

...the DB2 Analyzer will create a link to the stored procedure located in the PUBS schema (current schema) and NOT to the stored procedure located in the CASTPUBS schema (schema in the current path). This is because the analyzer will first search for relevant objects in the schema in which the analyzed object is located (in this case "PUBS") before searching in the current path (in this case CASTPUBS).

Table and View Indexes

The diagnostic calculation on indexes are not executed on derived tables that are in the FROM clause. The diagnostic calculations on indexes are not executed on views that are in the FROM clause. Note however that the statement that is inside the view is correctly analyzed. This limitation is due to the fact that CAST does not check whether there is an index on the view or not.

Built-in functions (E.g. Decode) are not taken into account by the following metrics:

  • Avoid SQL queries not using the first column of a composite index in the WHERE clause
  • Avoid SQL queries using functions on indexed columns in the WHERE clause
  • Avoid SQL queries on XXL tables using functions on indexed columns in the WHERE clause

As a consequence, when a built-in function is used in a WHERE clause of a query and an indexed column is passed as parameter to the function, the object containing the query is not reported as Very High Risk Object by the above listed metrics.

Implicit conversion

The IMPLICIT conversion diagnostic is only calculated for table or view columns. If the l-value or the r-value are complex, this diagnostic is ignored when at least one of the expressions cannot be evaluated to a unique identifier.

Nested sub-queries

Nested sub-queries are always processed prior to the query in which they are embedded. This causes the analyzer to store diagnostic violations that belong to the sub-query first. Table sources defined in a FROM clause of a given select statement are ignored when they are referenced inside a sub query.Depending on the query being executed, this may produce:

  1. An incorrect CARTESIAN JOIN diagnostic
  2. An incorrect UNUSED index diagnostic

The following example illustrates this limitation:Table source T1 is referenced inside a sub query through T1.a=T2.b. T1.a is ignored.

Select * from T1
Where not exists (select * from T2 where T1.a=T2.b)

Cartesian join

The Cartesian join diagnostic checks the use of at least one column of table sources in the FROM clause. However, this diagnostic doesn't take into account whether columns of the table sources in the FROM clause are joined together through a search condition or not.
The diagnostic calculation on the Cartesian product is not executed where there is a Cartesian product between two derived tables

The table below lists query types and indicates for each type whether the diagnostic calculation on the Cartesian product is executed:

Query

Diagnostic calculation on the Cartesian join

select * from Keys,Acc where '1'='toto';

YES

select * from Keys,Acc ;

YES

select * from Keys,Acc,AccBook where KeyNam='toto';

YES

select * from Keys,Acc where KeyNam='toto';

NO

select * from Keys,Acc where Keys.KeyNam='toto' and Acc.IdAcc>0;

NO

Examples

The following examples will not produce a CARTESIAN join because at least one column of the table sources in the FROM clause has been used:

 

SELECT t1.a, t2.b FROM T1 t1, T2 t2 WHERE t1.c>2 AND t2.d=2
SELECT t1.a, t2.b FROM T1 t1, T2 t2 WHERE t1.c = 2

The following example will not produce a CARTESIAN join because at least one column of each table source in the FROM clause has been used by joining them through a search condition.

SELECT t1.a, t2.b FROM T1 t1, T2 t2 WHERE t1.c = t2.d

The following example will produce a CARTESIAN join because no column of table source in the FROM clause has been used.

SELECT t1.a, t2.b FROM T1 t1, T2 t2

Algebric operations

This issue is limited to algebraic operations that use a table source column as an operand. An Algebraic operation is an operation using the following operators: +, -, *, / Example: column1 of table is added to 100 in an algebraic operation

table.column1 + 100

The diagnostic listed below is always calculated when the operand is an indexed column:

  • "Avoid SQL queries using functions on indexed columns in the WHERE clause"

In this case, the diagnosis is issued to indicate that an operation is being performed on an indexed column.

 

  • No labels