SQLCNT Parm for COBOL Program Scanner

search cancel

SQLCNT Parm for COBOL Program Scanner

book

Article ID: 24634

calendar_today

Updated On:

Products

Repository

Issue/Introduction

A new parm, &SQLCNT, was added to the SCANXT and SCANXT2 jcl to control how the scanner counts SELECT, INSERT, DELETE, UPDATE, & LOGIC uses of DB2 columns for the PRG DB2 relationship.

Environment

7.2

Repository Webstation Option

Resolution

The PRG DB2 Relationship and the &SQLCNT Parameter

The Repository COBOL scanner recognizes embedded SQL and identifies its components (such as tables and columns) and records this information in the Repository. One of the ways the scanner forms links between the PROGRAM entity and the DB2 entities is to create relationship instances between the COLUMNs of DB2 tables and the PROGRAM entities which it creates in the Repository. These PRG DB2 relationships are part of the RECORDS (note the plural) Dialog.

The PRG DB2 relationship contains attributes SELECT, INSERT, DELETE, UPDATE and LOGIC.
These attributes are used to record counts for the target COLUMN over the whole of the source PROGRAM.

There are now two methodologies for keeping these counts. You choose which one you want to use via the new &SQLCNT parameter on the SCANXT or SCANXT2 COBOL (and COBOL II respectively) scanning jobs found in SAMPJCL.
```
SQLCNT=S     S = SQL ORIENTED PRG DB2 COUNTS 
             T = TABLE ORIENTED PRG DB2 COUNTS 
```
Since the "S" method was the original, the program functionality has been written so that if no parameter is entered the parameter will default to "S". Hence, older JCL without any parameter at all (&SQLCNT is currently the last parameter in the string) should continue to work, as long as the customer is satisfied with the methodology implied by the "S" parameter.

The SCANXT jobs parse COBOL listings into 13 separate SCANCOB files, so called because they have the form <userid.SCANCOB.XXX>. SCANCOB.SQL is the file relevant to the creation of PRG DB2 relationships in Repository. The SCANCOB files are then read and loaded into the Repository by a second phase job which runs program DBXSCNC (also the member name in SAMPJCL). Neither DBXSCNC nor its attendant JCL require any change to implement the new TABLE oriented counting. The new methodology is completely contained within SCANXT & SCANXT2.
SQL oriented counting

The older, "S", methodology has a more administrative flavor. SQL statements are categorized as either SELECT, INSERT, DELETE, UPDATE or LOGIC (WHERE.. clauses etc.), and the columns are counted insofar as they appear in a SELECT, INSERT, DELETE or UPDATE or LOGIC statement, regardless of the function of the particular column within the query in question. Columns appearing in statements which are nested are counted once against the total for the type of the nesting statement and then again for the type of the nested statement.

Hence, given the query:
```
INSERT INTO SESSION.ACAT_ACCOUNTS 
  SELECT OFFICE_NO CONCAT ACOUNT_NO 
      , ADP_SECURITY_NO 
 FROM MDPOSNTB 
   WHERE CLIENT_CD = '27' 
    AND ADP_SECURITY_NO IN ( 'A000849' , '9999315') 
                                       (Example I )
```
Because the INSERT statement incorporates the whole query every column in the query will be counted as INSERT (the INSERT attribute of the corresponding PRG DB2 relationship between that column and the PROGRAM will be incremented). The columns OFFICE_NO, ACOUNT_NO and ADP_SECURITY_NO of the table MDPOSNTB would be counted in the INSERT tally of the PRG DB2 relationship between themselves and the PROGRAM.

This will be repeated for any other queries in the PROGRAM in which that column appears (there is only one PRG DB2 per column) so that the final "INSERT" tally would reflect the total number of occurrences of the target COLUMN in INSERT statements throughout the source PROGRAM.

Within the overall INSERT statement there is a SELECT statement, which itself contains a LOGIC statement (a WHERE clause). COLUMNs appearing in each nested statement in turn will be counted according to the type of the statement. Hence, ADP_SECURITY_NO appears twice in the enveloping INSERT statement and will get an INSERT count of 2. It also appears twice in the nested SELECT statement, so it also gets a SELECT count of 2, and then it appears once in the LOGIC statement which is nested in the SELECT statement, so it gets an additional count of LOGIC = 1.

Over and above the obvious double and triple counting, it is important to note that the count for a COLUMN is based on the type of statement the column is used in, not the use of the column within the query. In the example above, OFFICE_NO and ACCOUNT_NO are scored as INSERT even though OFFICE_NO and ACOUNT_NO themselves are only being SELECTed, and only their values are being INSERTed into an entirely different table (ACAT_ACCOUNTS). More important, the implication is that their table MDPOSNTB is being INSERTed into, which is not true.
TABLE oriented scanning

The SQL approach proved less than useful to some customers who were interested in discovering which of their PROGRAMs actually altered (INSERTed, UPDATEd or DELETEd) which of their TABLES and which PROGRAMs merely read them. In the above example, the program clearly alters the ACAT_ACCOUNTS table but does not alter the MDPOSNTB table in any way.

Hence the newer methodology is designated as "T", for TABLE oriented, as it is thought to be more focused on the TABLEs and their COLUMNS rather than on the SQL statements and their uses.

In this "T" methodology, the program will increment the INSERT counter for the columns in ACAT_ACCOUNTS, the SELECT counter for the columns named in SELECT FROM...MDPOSNTB, and the LOGIC counter for the columns in the WHERE clause. The INSERT counter needs to be able to determine the names of the columns in table ACAT_ACCOUNTS, even though they are not named directly in the query. See below for details.
EXAMPLE I

When an INSERT is performed, an entire row is inserted into the table. Thus, every column in the table is created with every insert, whether or not values are specified for each column. The form which the DBXSCNC program expects in SCANCOB.SQL therefore, is <table>.* for an INSERT. Assuming the table has been previously CATSYNCed into the Repository, DBXSCNC is then capable of supplying the individual column names based on the information in the Repository.

Hence, the results for the old functionality, for the INSERT statement presented above was (and is):
```
SQLCNT = S
 
Column name                   sel  ins   del  up  log
 
*                             0000,0001,0000,0000,0000 
OFFICE_NO                     0001,0001,0000,0000,0000 
ACOUNT_NO                     0001,0001,0000,0000,0000 
ADP_SECURITY_NO               0002,0002,0000,0000,0001 
CLIENT_CD                     0001,0001,0000,0000,0001  
```
I have inserted commas between the counts for clarity, but this is approximately what users should expect to see in their SCANCOB.SQL file should they scan in a program which contained only the above query.

The * stands for the columns of SESSIONS.ACAT_ACOUNTS. They are counted as INSERTED as they should be. The problem was that the subsequent four Columns, which are in fact in the MDPOSNTB table, are also counted as INSERTED, as well as SELECTED. And ADP_SECURITY_NO is counted as INSERTed and SELECTed twice, as well as appearing in a LOGIC statement once.
```
&SQLCNT = T
 
Column name                   sel  ins   del  up  log
 
*                             0000,0001,0000,0000,0000 
OFFICE_NO                     0001,0000,0000,0000,0000 
ACOUNT_NO                     0001,0000,0000,0000,0000 
ADP_SECURITY_NO               0001,0000,0000,0000,0001 
CLIENT_CD                     0000,0000,0000,0000,0001
```
Notice that "*" is still counted as INSERTED, but the next three columns are only counted as SELECTED and only once. ADP_SECURITY_NO is also counted as LOGIC (WHERE) and CLIENT_CD is only counted as logic since it does not appear in the SELECT as such.

This, then, is the basic "NEW" functionality.
EXAMPLE II

The two methodologies do not always differ in the counts they come up with for the same query.
If we scan in the query:

DELETE FROM SESSIONS.ACAT_ACCOUNTS
WHERE SECURITY_NO = '27'
(Example II)
We get the same results either way.

In the old functionality this would result in
```
Column name                   sel  ins   del  up  log
 
*                             0000,0000,0001,0000,0000 
SECURITY_NO                   0000,0000,0000,0000,0001 
```
Where * is all columns in SESSIONS.ACAT_ACCOUNTS, which are all DELETEd, and SECURITY_NO is the column from SESSIONS.ACAT_ACCOUNTS which is used to identity the row to be DELETEd. It has been counted as appearing once in a DELETE statement and once in a LOGIC statement as per the old methodology.

Notice, the layout above is that which one would see (with commas inserted for clarity) in the intermediate file, SCANCOB.SQL. Once the data have been uploaded to the Repository via the DBXSCNC program, the actual layout observed by bring up the PROGRAM and doing a GOTO PRG DB2 would be:
```
                         SELE INSE DELE UPDA LOGI
------------------------ -------- ------ ---- ----
.ACCOUNT_NO               0    0    1    0    0 
.SECURITY_NO              0    0    1    0    1 
```
Where the DELETE count for SECURITY_NO as included in * and the LOGIC count for it as counted separately in the SCANCOB file have been combined into one instance.

For this very simple example, the new "T" functionality would come out exactly the same. The two columns would be counted as DELETEd, because, obviously, they are, and SECURITY_NO would additionally be counted as LOGIC.

Feedback

thumb_up Yes

thumb_down No