Schlagwort: AI

OK, I kept you all waiting long enough… Here are my AI results with Db2 13 FL501!

Start at the Start

We begin with the beginning as last time:

Let’s get Connected!

Here you can see that I have already defined my little test Db2 13 system to the system:

Join the Dots …

Now just click on the vertical dots:

Here you can Disconnect, Edit (Which shows you the same window as “add connection”), List AI objects or Delete.

What do we have?

Choosing List AI objects you see what has been created:

Clicking on the down arrow on the left-hand side to expand looks a lot better than last month:

Clickedy-click-click

Now, clicking on the vertical dots on the right hand side, you can choose to Disable AI query or Enable AI query. (I have actually added a new column for consideration, so first I clicked on Disable and then clicked again on Enable)

Just the Facts, Ma’am – Again

Here you must make your “Usual Suspects” decision: which columns to actually use in building the AI Model. I am using our WorkLoadExpert performance table in this newsletter and have selected 17 columns that I think will work together nicely. Only one can be a “Key” column – I choose STMT_ID in this case. Once you are done selecting columns, click on the big blue “Next” button where you may then add additional filters to remove any rows you know are to be ignored:

Playing Chicken?

When done, click on the big blue “Enable” button and you get your last chance to chicken out:

SIO and CPU Records!

Click here and then get a cup of coffee….or go to SDSF and marvel at how much CPU and IO Spark actually uses and does this as the light bulbs dim in your part of the world…

You Keep me Spinning

Oddly, at least when I do this, the Initializing spinning wheels:

Right Round and Around

… never stop. The WLM Stored procedure for utilities was finally kicked off about 40 minutes later:

                    J E S 2  J O B  L O G  --  S Y S T E M  
                                                             
10.43.19 STC09611 ---- WEDNESDAY, 01 MAR 2023 ----           
10.43.19 STC09611  $HASP373 DD10WLMU STARTED                 
10.43.19 STC09611  IEF403I DD10WLMU - STARTED - TIME=10.43.19
10.43.19 STC09611  ICH70001I SQLDIID  LAST ACCESS AT 09:37:37

A Loaded Question?

And loaded all the required data:

ICE134I 0 NUMBER OF BYTES SORTED: 99083595                  
ICE253I 0 RECORDS SORTED - PROCESSED: 49173, EXPECTED: 49173

A quick exit and re-logon to the web interface…and Tra la!

Not only AI but Dr Who!

It is also strange that it seems to be in a time machine, one hour in advance of my local…Anyways, my new data is there and so onward! (I have since heard that our time zone setting is actually to blame and that just going back one level, and then forward again, stops the spinning wheel problem. However, just wait until Spark finishes and the stored procedure has loaded your data!)

Never Trust a Statistic You haven’t Faked Yourself!

Clicking on Data statistics shows:

Influencer of the Day?

Then you can look at the Column influence:

Super Model?

Back at the top you can then review the Model details:

Or just a Cluster….

Here are the Cluster center details:

Going back to the List AI Objects window, there are two blue buttons: Add object and Run query. I did not discuss Run Query last month but it gives you a SPUFI-like ability on the PC, tailored to the AI BiFs:

Lets RUN Away!

Clicking on Query type gives a drop-down list of the basic AI BiFs where it then gives you an example SQL (based on the documentation, *not* on any AI Tables you might have done!). Once you type in any query the “run” box turns blue:

It Works!

Click run and see the results:

Data Review

Once the model is trained, you can then review on the host what it has done. In SPUFI you can find details of what you have done in the pseudo Db2 catalog tables that support Data Insights, (I have removed a ton of rows to make this readable – sort of!):

SELECT * FROM 
SYSAIDB.SYSAIOBJECTS ; 
---------+---------+---------+---------+---------+---------+---------+---------+-------+-
              OBJECT_ID  OBJECT_NAME                       OBJECT_TYPE  SCHEMA  NAME     
---------+---------+---------+---------+---------+---------+---------+---------+-------+-
                     26  --------------------------------  T            IQA061QB IQATW001

-------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
STATUS                   CONFIGURATION_ID                 MODEL_ID  CREATED_BY                        CREATED_DATE              
-------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
Enabled                                36                       36  SQLDIID                           2023-02-24-07.57.42.086932

-------+---------+---------+---------+---------+---------+---------+---------+
LAST_UPDATED_BY                   LAST_UPDATED_DATE           DESCRIPTION     
-------+---------+---------+---------+---------+---------+---------+---------+
SQLDIID                           2023-03-01-10.43.38.407460  ----------------



SELECT * FROM  
SYSAIDB.SYSAICONFIGURATIONS ; 
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-----
       CONFIGURATION_ID  NAME                                            OBJECT_ID  RETRAIN_INTERVAL  KEEP_ROWIDENTIFIER_KEY 
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-----
                     36  --------------------------------                       26  ----------------  Y                      
---+---------+-------
NEGLECT_VALUES       
---+---------+-------
                     
---+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------
CREATED_BY                        CREATED_DATE                LAST_UPDATED_BY                   LAST_UPDATED_DATE         
---+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------
SQLDIID                           2023-03-01-09.51.00.994421  SQLDIID                           2023-03-01-09.51.00.994461



SELECT * FROM 
SYSAIDB.SYSAICOLUMNCONFIG
ORDER BY 1 , 3 , 2 ;   

--+---------+---------+---------+---------+---------+---------+---------+---------+---------+
CONFIGURATION_ID  COLUMN_NAME              COLUMN_AISQL_TYPE  COLUMN_PRIORITY  NEGLECT_VALUES
--+---------+---------+---------+---------+---------+---------+---------+---------+---------+
              36  END_USERID               C                  H                              
              36  PRIM_AUTHOR              C                  H                              
              36  PROGRAM                  C                  H                              
              36  REF_TABLE                C                  H                              
              36  REF_TAB_QUAL             C                  H                              
              36  STMT_ORIGIN              C                  H                              
              36  STMT_TEXT                C                  H                              
              36  TRANSACTION              C                  H                              
              36  WORKSTATION              C                  H                              
              36  COPIES_NO                I                  H 
.
.
.                             
              36  WLX_TYPE                 I                  H                              
              36  WORKSTATION_OLD          I                  H                              
              36  STMT_ID                  K                  H                              
              36  CPU_TIME                 N                  H                              
              36  ELAPSE_TIME              N                  H                              
              36  EXECUTIONS               N                  H                              
              36  GETP_OPERATIONS          N                  H                              
              36  ROWS_EXAMINED            N                  H                              
              36  ROWS_PROCESSED           N                  H                              
              36  STMT_LENGTH              N                  H                              

When the column COLUMN_AISQL_TYPE has a value of “I” it means it is ignored by AI processing. Also note that this table SYSAICOLUMNCONFIG gets two extra columns (COLUMN_VECTOR_CARDINALITY and MAX_DATA_VALUE_LEN) once you apply the vector prefetch upgrade APARs:

1. For IBM Z AI Optimization (zAIO) library and IBM Z AI Embedded (zADE) library in the IBM Z Deep Neural Network (zDNN) stack on z/OS:
   • Apply OA63950 and OA63952 for z/OS 2.5 (HZAI250).
   • Apply OA63949 and OA63951 for z/OS 2.4 (HBB77C0).
2. For OpenBLAS on z/OS:
   • Apply PH49807 and PH50872 for both z/OS 2.5 and z/OS 2.4 (HTV77C0).
   • Apply PH50881 for z/OS 2.5 (HLE77D0).
   • Apply PH50880 for z/OS 2.4 (HLE77C0).
3. For Db2 13 for z/OS, apply PH51892. Follow the instructions for DDL migration outlined in the ++ HOLD text. By default, the new Db2 subsystem parameter MXAIDTCACH is set to 0, indicating that vector prefetch is disabled. To enable vector prefetch, set MXAIDTCACH to a value between 1 and 512. This parameter is online changeable. See “IBM Db2 13 for z/OS documentation” on MXAIDTCACH.
4. For SQL Data Insights 1.1.0 UI and model training (HDBDD18), apply PH51052.

Further, the table SYSAIMODELS got a new column MODEL_CODE_LEVEL and an increase in size for the METRIC column to 500K with the above APARs.

SELECT * FROM  
SYSAIDB.SYSAIMODELS ; 
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
               MODEL_ID  NAME                                            OBJECT_ID         CONFIGURATION_ID  VECTOR_TABLE_CREATOR
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
                     36  --------------------------------                       26                       36  DSNAIDB             

+---------+---------+--
VECTOR_TABLE_NAME      
+---------+---------+--
AIDB_IQA061QB_IQATW001

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
VECTOR_TABLE_STATUS  VECTOR_TABLE_DBID  VECTOR_TABLE_OBID  VECTOR_TABLE_IXDBID  VECTOR_TABLE_IXOBID  VECTOR_TABLE_VERSION
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
A                                  329                  3                  329                    4                     1

-------+---------+---------+---------+---------+---------+---------+---------+-------
METRICS                                                                              
-------+---------+---------+---------+---------+---------+---------+---------+-------
[{"discriminator":8.59443984950101,"influence":0.9367419701380996,"name":"TRANSACTION",

-------+---------+---------+
INTERPRETABILITY_STRUCT     
-------+---------+---------+

-------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
CREATED_BY                        CREATED_DATE                LAST_UPDATED_BY                   LAST_UPDATED_DATE         
-------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
SQLDIID                           2023-03-01-09.51.03.777504  SQLDIID                           2023-03-01-10.43.37.796847

---+---------+---------+---------+---------+--
MODEL_ROWID                                   
---+---------+---------+---------+---------+--
2495E518C773B081E09C018000000100000000002213  



SELECT * FROM                            
SYSAIDB.SYSAICOLUMNCENTERS 
ORDER BY 1 , 2 , 3 ;
----+---------+---------+---------+---------+---------+---------+
MODEL_ID  COLUMN_NAME                          CLUSTER_MIN  LABEL
----+---------+---------+---------+---------+---------+---------+
      36  CPU_TIME                 -0.7200000000000000E+76  EMPTY
      36  CPU_TIME                 +0.0               E+00  c0   
      36  CPU_TIME                 +0.2000000000000000E+01  c1   
      36  CPU_TIME                 +0.1617671400000000E+08  c9   
      36  ELAPSE_TIME              -0.7200000000000000E+76  EMPTY
      36  ELAPSE_TIME              +0.0               E+00  c0   
      36  ELAPSE_TIME              +0.2000000000000000E+01  c1   
      36  ELAPSE_TIME              +0.1008466600000000E+08  c9   
      36  ELAPSE_TIME              +0.1074954980000000E+09  c10  

SELECT * FROM  
SYSAIDB.SYSAITRAININGJOBS ; 
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------+-------
        TRAINING_JOB_ID                OBJECT_ID         CONFIGURATION_ID                 MODEL_ID  STATUS  PROGRESS RESOURCE 
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------+-------
                     33                       26                       33                       33  F              0 
                     34                       26                       34                       34  F              0 
                     35                       26                       35                       35  C            100 
                     36                       26                       36                       36  C            100 


-+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+------
MESSAGES                                                                                                                        
-+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+------
{"messages":"failed to train model: Something went wrong with the zLoad, please check the SQL DI log for more details.","resumeI
{"messages":"failed to train model: Something went wrong with the zLoad, please check the SQL DI log for more details.","resumeI
{"messages":"model training is completed","sparkSubmitId":"driver-20230224105851-0002"}                                         
{"messages":"model training is completed","sparkSubmitId":"driver-20230301085133-0003"}                                         

-+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
START_TIME                  END_TIME                    CREATED_BY                        CREATED_DATE              
-+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
2023-02-24-08.01.20.737455  2023-02-24-08.51.56.386011  SQLDIID                           2023-02-24-08.01.20.737455
2023-02-24-10.52.27.687965  2023-02-24-11.43.22.095144  SQLDIID                           2023-02-24-10.52.27.687965
2023-02-24-11.58.20.109571  2023-02-24-12.49.20.660143  SQLDIID                           2023-02-24-11.58.20.109571
2023-03-01-09.51.03.777662  2023-03-01-10.43.38.407414  SQLDIID                           2023-03-01-09.51.03.777662

---+---------+---------+---------+---------+---------+------
LAST_UPDATED_BY                   LAST_UPDATED_DATE         
---+---------+---------+---------+---------+---------+------
SQLDIID                           2023-02-24-08.51.56.386030
SQLDIID                           2023-02-24-11.43.22.095164
SQLDIID                           2023-02-24-12.49.20.660160
SQLDIID                           2023-03-01-10.43.38.407425

KPIs from my Data

Here are a few KPIs from these first test runs:

SELECT COUNT(*) FROM IQA061QB.IQATW001 ;
      64390                                                 

SELECT COUNT(*) FROM DSNAIDB.AIDB_IQA061QB_IQATW001 ;
      49173                                                 

SELECT  SUBSTR(A.COLUMN_NAME, 1, 12) AS COLUMN_NAME 
      , SUBSTR(A.VALUE      , 1, 12) AS VALUE 
      , A.VECTOR 
FROM DSNAIDB.AIDB_IQA061QB_IQATW001  A 
ORDER BY 1 , 2 ; 

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--
COLUMN_NAME   VALUE         VECTOR                                                                                                  
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--
CPU_TIME      c0            3E594822BC9D2C7A3CD4F61DBD37E5033D34B314BD4CF8E3BD4B4D47BCB6CE293D1DBA1A3D858FDF3DC4DF08BD9E77753CCED43F
CPU_TIME      c1            3D9214383CFE4C90BDB3DFE4BBE407563BBA69553DB48FEFBCF39451BC6BABF0BDA31BDFBDB52F883C30B992BC8D71AF3D9E54FF
ELAPSE_TIME   c0            3E55B744BCCC5CED3D129B14BC9E553C3C9B121EBD8949C0BD4F838DBD1582A33D36D6363DA1F72F3DBCB033BDAFB88F3D4DE348
ELAPSE_TIME   c1            3DE390AC3D2DCC98BD2DF437BC5B7F713D766D103BD1AC10BB48E2C43B9FA9E6BD80D5D7BDC40AFE3CE586C9BCACADE93DFE2745
END_USERID    BOXWEL2       3D505075BD80E40F3D3AAB60BBA463F6BBCC51C43D92B118BD044D20BD8C6B3B3CC315133BBB087A3DC1D5923DC4EB763D039C8B
END_USERID    BOXWEL3       3D2FB919BC5013E3BD6652DDBD4654DA3DA4AC83BA70024FBD7FAFD0BCF16670BB2CCB4B3DBE32E93DFE13383CB052283C82FD46

As I mentioned last month the vector tables are very “special”!

What now?

So now we have analyzed a bunch of SQL WorkLoadExpert data from our own labs. What can we do?

First up, I wish to see what user KKKKKKK does with dynamic SQL that is “similar” to what I do with table IQATW001 but I am only interested in those SQLs where the AI thinks it is more than 0.5 (so very analogous):

SELECT AI_ANALOGY('BOXWEL3'  USING MODEL COLUMN PRIM_AUTHOR,  
                  'IQATW001' USING MODEL COLUMN REF_TABLE  ,  
                  'KKKKKKK'  USING MODEL COLUMN PRIM_AUTHOR,  
                  REF_TABLE ) AS AI_VALUE                     
      ,A.WLX_TIMESTAMP                                        
      ,A.STMT_ID                                              
      ,A.STMT_TIMESTAMP                                       
      ,SUBSTR(A.PRIM_AUTHOR , 1 , 8 ) AS PRIM_AUTHOR          
      ,SUBSTR(A.PROGRAM     , 1 , 8 ) AS PROGRAM              
      ,SUBSTR(A.REF_TABLE   , 1 , 18) AS REF_TABLE            
      ,A.EXECUTIONS                                           
      ,A.GETP_OPERATIONS                                      
      ,A.ELAPSE_TIME                                          
      ,A.CPU_TIME                                             
      ,A.STMT_TEXT                                            
FROM IQA061QB.IQATW001 A                                      
WHERE A.PRIM_AUTHOR = 'KKKKKKK'                               
  AND  AI_ANALOGY('BOXWEL3'    USING MODEL COLUMN PRIM_AUTHOR,
                  'IQATW001'   USING MODEL COLUMN REF_TABLE  ,
                  'KKKKKKK'    USING MODEL COLUMN PRIM_AUTHOR,
                  REF_TABLE )                                 
       > 0.5                                                  
ORDER BY 1 DESC -- SHOW BEST FIRST                            
--ORDER BY 1 -- SHOW WORST FIRST                              
FETCH FIRST 2000 ROWS ONLY ;                                  

And the results:

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-------
               AI_VALUE  WLX_TIMESTAMP                               STMT_ID  STMT_TIMESTAMP              PRIM_AUTHOR
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-------
+0.7875136583708362E+00  2022-12-16-11.43.46.365129                    54801  2023-01-05-14.23.34.706136  KKKKKKK    
+0.7875136583708362E+00  2022-12-16-11.43.46.365129                    54800  2023-01-05-14.23.34.704234  KKKKKKK    
+0.7875136583708362E+00  2022-12-16-11.43.46.365129                    43654  2023-01-05-08.09.18.888198  KKKKKKK    
+0.7875136583708362E+00  2022-12-16-11.43.46.365129                    43653  2023-01-05-08.09.18.832308  KKKKKKK    
+0.7754887840772942E+00  2022-12-05-11.43.10.984618                     2616  2022-12-05-10.15.20.884139  KKKKKKK    
+0.7754887840772942E+00  2022-12-05-11.43.10.984618                     2609  2022-12-05-10.15.20.487031  KKKKKKK    
+0.7754887840772942E+00  2022-12-05-11.43.10.984618                     2617  2022-12-05-10.15.20.905752  KKKKKKK    
+0.7754887840772942E+00  2022-12-16-11.43.46.365129                    37239  2023-01-04-11.07.49.907438  KKKKKKK    
+0.7754887840772942E+00  2022-12-16-11.43.46.365129                    37230  2023-01-04-11.07.49.457189  KKKKKKK    
+0.7754887840772942E+00  2022-12-16-11.43.46.365129                    37237  2023-01-04-11.07.49.879457  KKKKKKK    
+0.7754887840772942E+00  2022-12-16-11.43.46.365129                    37238  2023-01-04-11.07.49.903769  KKKKKKK    
+0.7754887840772942E+00  2022-12-05-11.43.10.984618                     2618  2022-12-05-10.15.20.909552  KKKKKKK    
+0.7754887840772942E+00  2022-12-16-11.43.46.365129                    45396  2023-01-05-08.17.11.633089  KKKKKKK    
+0.7754887840772942E+00  2022-12-16-11.43.46.365129                    45389  2023-01-05-08.17.11.311055  KKKKKKK    
+0.7754887840772942E+00  2022-12-16-11.43.46.365129                    45397  2023-01-05-08.17.11.655514  KKKKKKK    

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--
PROGRAM   REF_TABLE                        EXECUTIONS          GETP_OPERATIONS              ELAPSE_TIME
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--
SEDBTIAA  R510T002                                  1                        0                        8
SEDBTIAA  R510T002                                  1                        0                        9
SEDBTIAA  R510T002                                  1                        0                       11
SEDBTIAA  R510T002                                  1                        3                       61
SEDBTIAA  IQATA001                                  0                        0                        0
SEDBTIAA  IQATA001                                  0                        0                        0
SEDBTIAA  IQATA001                                  0                        0                        0
SEDBTIAA  IQATA001                                  1                        4                       32
SEDBTIAA  IQATA001                                  1                        2                      111
SEDBTIAA  IQATA001                                  1                       12                    20749
SEDBTIAA  IQATA001                                  1                        4                       36
SEDBTIAA  IQATA001                                  0                        0                        0
SEDBTIAA  IQATA001                                  1                       12                    18571

All interesting stuff! I use dynamic SQL to INSERT into the table a lot, and it has determined that use of dynamic SQL with tables R510T002 and IQATA001 is analogous. In fact, it is! The SQLs were all INSERT, DELETE and UPDATE… Clever ol’ AI!

Dynamic Duo?

Now I wish to see which programs process dynamic SQL like the IBM DSNTIAD and DSNTIAP programs:

SELECT AI_SEMANTIC_CLUSTER( PROGRAM,             
                           'DSNTIAD',            
                           'DSNTIAP') AS AI_VALUE
      ,A.WLX_TIMESTAMP                           
      ,A.STMT_ID                                 
      ,A.STMT_TIMESTAMP                          
      ,SUBSTR(A.PROGRAM , 1 , 8) AS PROGRAM      
      ,A.EXECUTIONS                              
      ,A.GETP_OPERATIONS                         
      ,A.ELAPSE_TIME                             
      ,A.CPU_TIME                                
      ,A.STMT_TEXT                               
FROM IQA061QB.IQATW001 A                         
WHERE A.PROGRAM NOT IN ('DSNTIAD', 'DSNTIAP')    
  AND A.STMT_ORIGIN = 'D'                        
ORDER BY 1 DESC -- SHOW BEST FIRST                      
--ORDER BY 1 -- SHOW WORST FIRST                            
FETCH FIRST 10 ROWS ONLY ;                       

And the results:

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
               AI_VALUE  WLX_TIMESTAMP                               STMT_ID  STMT_TIMESTAMP              PROGRAM 
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
+0.7104441523551941E+00  2023-01-06-05.27.28.779825                      824  2023-01-17-09.13.48.940697  DSN§EP2L
+0.5050856471061707E+00  2023-01-06-05.27.28.779825                      559  2023-01-12-13.04.44.032345  O2DB81  
+0.5032740235328674E+00  2023-01-06-05.27.28.779825                      561  2023-01-12-13.04.44.075465  O2DB84  
+0.5007491707801819E+00  2023-01-06-05.27.28.779825                      560  2023-01-12-13.04.44.062180  O2DB82  
+0.4917877912521362E+00  2023-01-06-05.27.28.779825                      558  2023-01-12-13.04.43.986695  O2DB80  
+0.4652681946754456E+00  2023-01-06-05.27.28.779825                      562  2023-01-12-13.04.44.105563  O2DB85  
+0.4551711678504944E+00  2023-02-02-10.45.26.535375                        8  2023-01-17-16.51.06.825629  O2DB8X  
+0.4551711678504944E+00  2023-01-06-05.27.28.779825                        8  2023-01-17-16.51.06.825629  O2DB8X  
+0.4551711678504944E+00  2023-01-06-05.27.28.779825                      557  2023-01-12-13.04.40.846826  O2DB8X  
+0.4452087283134460E+00  2023-01-06-05.27.28.779825                        7  2023-01-17-16.50.54.118774  O2DB6X  

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
EXECUTIONS          GETP_OPERATIONS              ELAPSE_TIME                 CPU_TIME  STMT_TEXT                                    
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
         1                        0                        0                        0  INSERT INTO KKKKKKK.SAXDBT (SELECT A.NAME, 'B
         1                        0                       29                       29  SELECT COUNT(*),COALESCE(SUM(CASE EXCEPTION_C
         1                        0                       28                       24  SELECT COUNT(*),COALESCE(SUM(CASE EXCEPTION_C
         1                        0                       22                       22  SELECT COUNT(*),COALESCE(SUM(CASE EXCEPTION_C
         1                        0                       76                       43  SELECT COUNT(*),COALESCE(SUM(CASE EXCEPTION_C
         1                        0                       44                       24  SELECT COUNT(*),COALESCE(SUM(CASE EXCEPTION_C
         0                        0                        0                        0  SELECT COALESCE(COALESCE(A.DBNAME,B.DBNAME),C
         1                      215                   132887                    13462  SELECT COALESCE(COALESCE(A.DBNAME,B.DBNAME),C
         1                       18                     1035                      791  SELECT COALESCE(COALESCE(A.DBNAME,B.DBNAME),C
         2                        7                    27753                     1236  SELECT COALESCE(COALESCE(A.DBNAME,B.DBNAME),C

Again, very nice – it spotted all of the RealTime DBAExpert Dynamic SQL access programs in use…

Undynamic Duo?

Ok, now the opposite of that query, show me the SQLs that are like them but not them!

SELECT AI_SEMANTIC_CLUSTER( PROGRAM,                      
                           'DSNTIAD',                     
                           'IQADBACP',                    
                           'SEDBTIAA') AS AI_VALUE        
      ,A.WLX_TIMESTAMP                                    
      ,A.STMT_ID                                          
      ,A.STMT_TIMESTAMP                                   
      ,SUBSTR(A.PRIM_AUTHOR , 1 , 8) AS PRIM_AUTHOR       
      ,SUBSTR(A.PROGRAM , 1 , 8) AS PROGRAM               
      ,SUBSTR(A.REF_TABLE , 1 , 18) AS REF_TABLE          
      ,A.EXECUTIONS                                       
      ,A.GETP_OPERATIONS                                  
      ,A.ELAPSE_TIME                                      
      ,A.CPU_TIME                                         
      ,A.STMT_TEXT                                        
FROM IQA061QB.IQATW001 A                                  
WHERE A.PROGRAM NOT IN ('DSNTIAD', 'IQADBACP' ,'SEDBTIAA')
--AND A.STMT_ORIGIN = 'D'                                 
--ORDER BY 1 DESC -- SHOW BEST FIRST                      
ORDER BY 1 -- SHOW WORST FIRST                            
FETCH FIRST 10 ROWS ONLY ;                                

and the output:

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-------
STMT_ID  STMT_TIMESTAMP              PRIM_AUTHOR  PROGRAM   REF_TABLE                        EXECUTIONS          GETP_OPERATIONS
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-------
  39974  2023-01-11-09.15.29.336208               DSMSUMA   SYSVOLUMES                                3                        9
  39973  2023-01-11-09.15.29.336151               DSMSUMA   SYSDATABASE                               3                        6
  39976  2023-01-11-09.15.29.363187               DSMSUMA   SYSTABLEPART                              3                      408
  39975  2023-01-11-09.15.29.343490               DSMSUMA   SYSTABLES                                 3                      870
  39981  2023-01-11-09.15.29.459879               DSMSUMA   SYSPLAN                                   3                        9
  39980  2023-01-11-09.15.29.449311               DSMSUMA   SYSTABLESPACE                             3                      399
  39979  2023-01-11-09.15.29.381912               DSMSUMA   SYSINDEXES                                3                     1970
  39978  2023-01-11-09.15.29.370762               DSMSUMA   SYSINDEXES                                3                      786
  39977  2023-01-11-09.15.29.369892               DSMSUMA   SYSINDEXPART                              3                      381
  39972  2023-01-11-09.15.29.336020               DSMSUMA   SYSSTOGROUP                               3                        6

Aha! It found a little assembler program that fires off SQL like the top three!

The Apple doesn’t Fall far from the Tree

Finally, I want to see which programs behave like IQADBACP (our main dynamic SQL driver program):

SELECT AI_SIMILARITY( PROGRAM,              
                     'IQADBACP') AS AI_VALUE
      ,A.WLX_TIMESTAMP                      
      ,A.STMT_ID                            
      ,A.STMT_TIMESTAMP                     
      ,SUBSTR(A.PROGRAM , 1 , 8) AS PROGRAM 
      ,A.EXECUTIONS                         
      ,A.GETP_OPERATIONS                    
      ,A.ELAPSE_TIME                        
      ,A.CPU_TIME                           
      ,A.STMT_TEXT                          
FROM IQA061QB.IQATW001 A                    
WHERE NOT A.PROGRAM = 'IQADBACP'            
  AND A.STMT_ORIGIN = 'D'                   
ORDER BY 1 DESC -- SHOW BEST FIRST                      
--ORDER BY 1 -- SHOW WORST FIRST                            
FETCH FIRST 10 ROWS ONLY;                   

And the output:

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---
               AI_VALUE  WLX_TIMESTAMP                               STMT_ID  STMT_TIMESTAMP              PROGRAM
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---
+0.4575602412223816E+00  2023-02-02-10.45.26.535375                        7  2023-01-17-16.50.54.118774  O2DB6X 
+0.4575602412223816E+00  2023-01-06-05.27.28.779825                        7  2023-01-17-16.50.54.118774  O2DB6X 
+0.4400676488876343E+00  2023-01-06-05.27.28.779825                      220  2023-01-20-10.11.14.618038  DSMDSLC
+0.4400676488876343E+00  2023-01-06-05.27.28.779825                      222  2023-01-20-10.11.38.136712  DSMDSLC
+0.4400676488876343E+00  2023-01-06-05.27.28.779825                      221  2023-01-20-10.11.21.993833  DSMDSLC
+0.4400676488876343E+00  2023-01-06-05.27.28.779825                      252  2023-01-20-10.55.07.078652  DSMDSLC
+0.4400676488876343E+00  2023-01-06-05.27.28.779825                      251  2023-01-20-10.54.37.901247  DSMDSLC
+0.4400676488876343E+00  2023-01-06-05.27.28.779825                      233  2023-01-20-10.47.23.961076  DSMDSLC
+0.4400676488876343E+00  2023-01-06-05.27.28.779825                      232  2023-01-20-10.46.59.756430  DSMDSLC
+0.4400676488876343E+00  2023-01-06-05.27.28.779825                      224  2023-01-20-10.33.42.609175  DSMDSLC

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
EXECUTIONS          GETP_OPERATIONS              ELAPSE_TIME                 CPU_TIME  STMT_TEXT                                    
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
         0                        0                        0                        0  SELECT COALESCE(COALESCE(A.DBNAME,B.DBNAME),C
         2                        7                    27753                     1236  SELECT COALESCE(COALESCE(A.DBNAME,B.DBNAME),C
         2                     1387                    57974                    14900  SELECT CASE WHEN B.VCATNAME < ' ' THEN '00000
         6                     4170                    68943                    53330  SELECT CASE WHEN B.VCATNAME < ' ' THEN '00000
         6                     4596                   286233                    99773  SELECT CASE WHEN B.VCATNAME < ' ' THEN '00000
         1                      851                    55367                    42542  SELECT CASE WHEN B.VCATNAME < ' ' THEN '00000
         1                      298                   122961                    24848  SELECT CASE WHEN B.VCATNAME < ' ' THEN '00000
         2                     1260                    68272                    48952  SELECT CASE WHEN B.VCATNAME < ' ' THEN '00000
         1                      192                     3395                     2508  SELECT CASE WHEN B.VCATNAME < ' ' THEN '00000
         3                      810                    43520                    23771  SELECT CASE WHEN B.VCATNAME < ' ' THEN '00000

Again, it found all of the correct programs.

Quibble Time!

I did find some small problems…

I use ALIASes a lot and they appear in the drop-down selection box when in “Add object”, but if you choose one as an AI Object:

This then leads on to the second quibble… The red windowed error messages stay there until you click them away… This can lead you to believe that a problem exists when in reality everything is groovy!

I also found out that the spinning wheel completes if you wait for Spark and LOAD and then go back and forward on the panel.

Finally, the way you move around the product is a bit odd… sometimes you use the browser back function, sometimes you click on a “Back” button, sometimes you click on a bread crumb, sometimes there are multiple options hidden under triple vertical dots which change depending on where you are in the process.

I am sure these little UI bugs will all get ironed out very quickly!

End of Quibbles.

First Baby Steps Taken!

This little trip into the AI world is really just the tip of the iceberg. I will be doing many more AI queries over the coming months, and I hope to show all my results, either here or in another one of my Newsletters and/or at the German GUIDE in April 2023 and, hopefully, at the IDUG 2023 as well.

Any questions about AI, do not fear to ask, and when not me then ChatGPT!

TTFN

Roy Boxwell