View source for Useful SQL Queries for Content Manager OnDemand

{{TOCright}}
= 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 can be found in these tables.  Although the contents of the tables are only briefly documented, there is plenty of interesting data to be found inside them to help you solve problems and troubleshoot Content Manager OnDemand.

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.