Useful SQL Queries for Content Manager OnDemand

From CMOD.wiki
Jump to: navigation, search

SQL Queries for CMOD Administrators

This is a series of example queries, which will show you how to extract useful information for CMOD Administrators from Content Manager OnDemand, that you can't get from the OnDemand Administrative Client.

All the examples below were run on AIX with DB2. Long output is truncated with the string "--snip--" for brevity.

Start the DB2 Command Line Client

$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.1.4
--snip--

db2 => 

Next we need to create a connection to the database.

Connect to the Database

db2 => connect to archive

  Database Connection Information

Database server        = DB2/AIX64 10.1.4
SQL authorization ID   = ARCHIVE
Local database alias   = ARCHIVE

Which tables are available for us to query?

db2 => list tables for user

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
ACA1                            ARCHIVE         T     2015-05-21-12.05.47.942333
ADA1                            ARCHIVE         T     2015-05-21-11.54.41.841925
AFA1                            ARCHIVE         T     2015-05-10-09.15.57.588945
ARSAG                           ARCHIVE         T     2015-05-08-19.15.25.181919
ARSAG2FOL                       ARCHIVE         T     2015-05-08-19.15.25.349944
ARSAGFLD                        ARCHIVE         T     2015-05-08-19.15.25.452429
ARSAGFLDALIAS                   ARCHIVE         T     2015-05-08-19.15.25.528572
ARSAGINDEX                      ARCHIVE         T     2015-05-08-19.15.25.599789
ARSAGPERMS                      ARCHIVE         T     2015-05-08-19.15.25.671429
ARSANN                          ARCHIVE         T     2015-05-08-19.15.25.788716
ARSAPP                          ARCHIVE         T     2015-05-08-19.15.25.912664
ARSAPPUSR                       ARCHIVE         T     2015-05-08-19.15.26.042617
ARSCAB                          ARCHIVE         T     2015-05-08-19.15.26.164461
ARSCAB2FOL                      ARCHIVE         T     2015-05-08-19.15.26.259013
ARSCABPERMS                     ARCHIVE         T     2015-05-08-19.15.26.328841
ARSCFSODWORK                    ARCHIVE         T     2015-05-08-19.15.26.397442
ARSFOL                          ARCHIVE         T     2015-05-08-19.15.26.521362
ARSFOLFLD                       ARCHIVE         T     2015-05-08-19.15.26.607325
ARSFOLFLDUSR                    ARCHIVE         T     2015-05-08-19.15.26.676858
ARSFOLPERMS                     ARCHIVE         T     2015-05-08-19.15.26.748387
ARSGROUP                        ARCHIVE         T     2015-05-08-19.15.26.832365
ARSHOLD                         ARCHIVE         T     2015-05-08-19.15.26.917837
ARSHOLDMAP                      ARCHIVE         T     2015-05-08-19.15.27.005190
ARSHOLDPERMS                    ARCHIVE         T     2015-05-08-19.15.27.075496
ARSHOLDWORK                     ARCHIVE         T     2015-05-08-19.15.27.139762
ARSLOAD                         ARCHIVE         T     2015-05-08-19.15.27.218273
ARSNAMEQ                        ARCHIVE         T     2015-05-08-19.15.27.314828
ARSNODE                         ARCHIVE         T     2015-05-08-19.15.27.449770
ARSPRT                          ARCHIVE         T     2015-05-08-19.15.27.543703
ARSPRTOPTS                      ARCHIVE         T     2015-05-08-19.15.27.640705
ARSPRTUSR                       ARCHIVE         T     2015-05-08-19.15.27.778551
ARSRES                          ARCHIVE         T     2015-05-08-19.15.27.848941
ARSSEG                          ARCHIVE         T     2015-05-08-19.15.27.921419
ARSSET                          ARCHIVE         T     2015-05-08-19.15.28.016458
ARSSYS                          ARCHIVE         T     2015-05-08-19.15.28.100148
ARSUSER                         ARCHIVE         T     2015-05-08-19.15.28.195988
ARSUSRGRP                       ARCHIVE         T     2015-05-08-19.15.28.309638
ARSUSRGRPID                     ARCHIVE         T     2015-05-08-19.15.28.392670
AUA1                            ARCHIVE         T     2015-05-10-19.47.51.712855
BDA1                            ARCHIVE         T     2015-05-09-15.25.45.061520
BEA1                            ARCHIVE         T     2015-05-10-20.03.56.423789
BFA1                            ARCHIVE         T     2015-05-10-16.41.30.016719
BHA1                            ARCHIVE         T     2015-05-10-19.46.08.765555
--snip--

The tables starting with ARS are internal configuration tables for CMOD. All of the data you see inside the IBM CMOD client can be found in these tables. Although the contents of the tables are only briefly documented in the IBM CMOD documentation, there is plenty of interesting data to be found inside them to help you solve problems and troubleshoot issues. Check out the Content Manager OnDemand Database Tables for more information about the tables, fields, and their contents.

And what are those other table names? They contain the index data with the fields you define in Application Groups with the OnDemand Administrator Client. Keep reading!

What are the fields and types in ARSAG?

db2 => describe table arsag 

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
NAME                            SYSIBM    VARCHAR                     60     0 No    
DESCRIPTION                     SYSIBM    VARCHAR                    120     0 No    
AGID                            SYSIBM    INTEGER                      4     0 No    
AGID_NAME                       SYSIBM    VARCHAR                      8     0 No    
DB_SEG                          SYSIBM    CHARACTER                    1     0 No    
ANN_TYPE                        SYSIBM    CHARACTER                    1     0 No    
DB_EXP_DATE                     SYSIBM    INTEGER                      4     0 No    
DB_MGRT_DATE                    SYSIBM    INTEGER                      4     0 No    
SM_CACHE_DOC                    SYSIBM    INTEGER                      4     0 No    
RESGRP                          SYSIBM    INTEGER                      4     0 No    
LOAD_ID                         SYSIBM    INTEGER                      4     0 No    
LOG                             SYSIBM    INTEGER                      4     0 No    
LOAD_ID_SUFFIX                  SYSIBM    VARCHAR                      8     0 No    
MGRT_CACHE_DATE                 SYSIBM    INTEGER                      4     0 No    
REIMPORT_EXP_DATE               SYSIBM    INTEGER                      4     0 No    
SID                             SYSIBM    INTEGER                      4     0 No    
EXPIRE_TYPE                     SYSIBM    CHARACTER                    1     0 No    
SEG_ROWS                        SYSIBM    INTEGER                      4     0 No    
SEG_ID                          SYSIBM    INTEGER                      4     0 No    
OBJ_SIZE                        SYSIBM    INTEGER                      4     0 No    
TYPE                            SYSIBM    INTEGER                      4     0 No    
UPD_USERID                      SYSIBM    VARCHAR                    128     0 Yes   
UPD_DATE                        SYSIBM    BIGINT                       8     0 Yes   
LAST_DOC_DATE                   SYSIBM    BIGINT                       8     0 Yes   
MIGR_SRVR_STR                   SYSIBM    VARCHAR                    254     0 Yes   
SM_CACHE_DELTA                  SYSIBM    INTEGER                      4     0 Yes   
DATABASE_NAME                   SYSIBM    VARCHAR                      8     0 Yes   
SM_CACHE_RES                    SYSIBM    INTEGER                      4     0 Yes   

 28 record(s) selected.

These fields are all for internal use by CMOD to provide the functionality of the product. You can also see the data types (CHARACTER, VARCHAR, INTEGER) and the lengths of the fields. All these fields contain information you see in the Content Manager OnDemand Administrator Client -- but some are strictly to help CMOD work.

What are the AGID and AGID_NAME fields?

db2 => select agid,agid_name,name from arsag

AGID        AGID_NAME NAME                                                        
----------- --------- ------------------------------------------------------------
       5007 SA        System Load                                                 
       5001 SL        System Log                                                  

  2 record(s) selected.

The AGID is the 'number' of the Application Group. This number is used in other tables to establish a relationship -- for example, to Applications, or Storage Nodes, or Groups. The AGID_NAME is a three-character alphabetical label for the Application Group. This AGID_NAME field is used in the cache filesystem, database table names, and Storage Sets. In the case of Storage Sets, the AGID_NAME value is used as the 'Filespace Name' in Tivoli Storage Manager (now 'IBM Spectrum Archive').

How is the AGID used in Content Manager OnDemand?

If we describe the Application Table ARSAPP, we can see it also has an AGID field.

db2 => describe table arsapp

                               Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
NAME                            SYSIBM    VARCHAR                     60     0 No    
DESCRIPTION                     SYSIBM    VARCHAR                    120     0 No    
AGID                            SYSIBM    INTEGER                      4     0 No    
AID                             SYSIBM    INTEGER                      4     0 No    
DOC_TYPE                        SYSIBM    CHARACTER                    1     0 No    
--snip--

We can use this common field as a relation between tables, to build powerful queries.

db2 => select a.agid,a.agid_name,a.name,b.aid,b.name from arsag a, arsapp b where a.agid=b.agid

AGID        AGID_NAME NAME                                     AID         NAME                                                        
----------- --------- ---------------------------------------- ----------- -------------------------------
       5007 SA        System Load                                     5008 System Load - No Log Document                              
       5007 SA        System Load                                     5009 System Load - Log Document                                  
       5001 SL        System Log                                      5002 System Log - N/A Document                                   
       5001 SL        System Log                                      5003 System Log - No Document                                    
       5001 SL        System Log                                      5004 System Log - Text Document                                  

 5 record(s) selected.

The values on the left (from the ARSAG table) are repeated, because the values (AGIDs 5007 and 5001) appear multiple times in the ARSAPP table. Notice that Applications have their own unique identifiers as well, separate from AGIDs.

How many documents are in CMOD Application Groups?

You may want to know how many documents are currently in an Application Group. You can get this information by querying the ARSSEG table in the Content Manager OnDemand database.

This SQL Query provides the number of inserted and deleted rows from the Application Group Tables in OnDemand.

 db2 "select arsag.name,arsseg.table_name,arsseg.ins_rows,arsseg.del_rows from arsag,arsseg where arsag.agid=arsseg.agid order by arsseg.table_name"
 
 NAME                                                         TABLE_NAME         INS_ROWS    DEL_ROWS   
 ------------------------------------------------------------ ------------------ ----------- -----------
 ALPHA                                                        RQA1                      4454           0
 BRAVO                                                        RSA1                     19670           1
 CHARLIE                                                      RUA1                     18878           1
 DELTA                                                        RVA1                       153           5
 ECHO                                                         RWA1                       118          14
 FOXTROT                                                      RYA1                      7989           0
 GOLF                                                         SBA1                    195077          26
 HOTEL                                                        SDB1                    444632           0
 INDIA                                                        SIB1                      8228           0
 System Log                                                   SL10                   2500001           0 
 System Log                                                   SL11                   2500000           0
 System Log                                                   SL12                   2500002           0
 System Log                                                   SL13                   2500004           0

But maybe you don't want to do the math, and instead want DB2 to count the records for you, and summarize by Application Group, instead of listing each table:

 db2 "select arsag.name,sum(arsseg.ins_rows-arsseg.del_rows) as Total_Rows from arsag,arsseg where arsag.agid=arsseg.agid group by arsag.name"   
 
 NAME                                                         TOTAL_ROWS 
 ------------------------------------------------------------ -----------
 JULIET                                                           1015276
 KILO                                                               32133
 LIMA                                                               27200
 MONTREAL                                                           10726
 NANCY                                                                148
 OSCAR                                                              42072
 PAPA                                                                 886
 System Log                                                     241680991

Can I export this information to a spreadsheet?

Yup! Prefix your select statement with an export command, specifying the "Delimited" format, which uses commas (",") by default:

 db2 "export to AG_Row_Count.csv of DEL select arsag.name,sum(arsseg.ins_rows-arsseg.del_rows) as Total_Rows from arsag,arsseg where arsag.agid=arsseg.agid group by arsag.name"
 
 SQL3104N  The Export utility is beginning to export data to file "AG_Row_Count.csv".
 SQL3105N  The Export utility has finished exporting "205" rows.
 
 Number of rows exported: 205

Now let's check the results:

 $ ls -l AG_Row_Count.csv
 -rw-r--r--    1 archive  sysadm1        3847 Jul 05 09:52 AG_Row_Count.csv
 
 $ more AG_Row_Count.csv
 "QUEBEC",6198
 "ROMEO",12971
 "SIERRA",23032
 "TANGO",18902
 "UNIFORM",38344
 "VICTOR",56579


Related Links

Counting loads by Indexer from the IBM OnDemand User Group Forums