Useful SQL Queries for Content Manager OnDemand
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