Saturday, March 7, 2015

Netezza 6.0.8 P17 Documentation

For those looking for Netezza NPS 6.0.8 documentation, you can get it from here.  Have a nice reading.

Here are the list of manuals

twinfin_wiring.pdf
index.pdx
Netezza_system_configuration_guide_skimmer.pdf
Netezza_system_configuration_guide_ha_xs_mrack.pdf
Netezza_system_configuration_guide_ha_xs.pdf
Netezza_system_admin_guide.pdf
Netezza_stored_procedures_guide.pdf
Netezza_software_upgrade.pdf
Netezza_site_prep_specs_skimmer.pdf
Netezza_site_prep_specs.pdf
Netezza_release_notes.pdf
Netezza_odbc_jdbc_guide.pdf
Netezza_getting_started_tips.pdf
Netezza_database_users_guide.pdf
Netezza_data_loading_guide.pdf
Netezza_advanced_security_admin_guide.pdf
version.txt
index

Monday, February 23, 2015

Netezza General Purpose Scripts


Dear NZ Admin,

I have attached Netezza general purpose scripts from NPS4 to NPS 7 in pdf format.  You can download below.

The document is splitted into 3 parts.  You need to jon them using hjsplit.

Netezza General Purpose Scripts.pdf.001
Netezza General Purpose Scripts.pdf.002
Netezza General Purpose Scripts.pdf.003

Sunday, November 16, 2014

Finding table with Max SPU disk

Often time, when you issue a nz_storage_stats command or from nzadmin GUI under NPS, you noticed the following info

 Minimun | Average | Maximum
---------+---------+---------
 42.591  | 43.241  | 49.801


You can get the output above from the query below:

        SELECT
                TO_CHAR ( min_disk_usage , 'FM999,999,999,990.000' ),
                TO_CHAR ( avg_disk_usage , 'FM999,999,999,990.000' ),
                TO_CHAR ( max_disk_usage , 'FM999,999,999,990.000' )
        FROM
        (
       SELECT
               100 * MIN ( sds_used::numeric(38,10) / (_v_spudevicestate.sds_size::bigint * (1024^2))  ) AS min_disk_usage,
               100 * AVG ( sds_used::numeric(38,10) / (_v_spudevicestate.sds_size::bigint * (1024^2))  ) AS avg_disk_usage,
               100 * MAX ( sds_used::numeric(38,10) / (_v_spudevicestate.sds_size::bigint * (1024^2))  ) AS max_disk_usage
       FROM    _v_dslice
               left outer join _v_spudevicestate on
               ( _v_dslice.ds_prihwid  = _v_spudevicestate.sds_hwid AND
                 _v_dslice.ds_priptid  = _v_spudevicestate.sds_device )
        )
        AS the_subselect;

When, you have this info, how do you find out which table having this Max SPU disk usage.  Below you can determine the table name for the Max data slice.

with
GetMaxDSlice (DS_ID, DS_PCT, ROW_NUM) as
(select ds.ds_id as DS_ID,
(((sp.EXTENTS_USED * 3)/(sp.EXTENT_COUNT * 3)::float)*100)::numeric(10,2) as DS_PCT,
row_number () over (order by (((sp.EXTENTS_USED * 3)/(sp.EXTENT_COUNT * 3)::float)*100)::numeric(10,2) desc ) as Row_Num
FROM _v_dslice ds  LEFT OUTER JOIN _v_spudevicemap prm on (ds.ds_prihwid = prm.sdm_spuhwid AND ds.ds_priptid = prm.sdm_spudevice AND prm.sdm_role = 0)  LEFT OUTER JOIN _v_spudevicemap mir on (ds.ds_prihwid = mir.sdm_spuhwid AND ds.ds_priptid = mir.sdm_spudevice AND mir.sdm_role = 1)  LEFT OUTER JOIN _v_hwcomp hwp on (prm.sdm_sdhwid = hwp.hw_hwid)  LEFT OUTER JOIN _v_hwcomp hwm on (mir.sdm_sdhwid = hwm.hw_hwid)  LEFT OUTER JOIN _vt_disk_partition sp on (ds.ds_prihwid = sp.HWID AND ds.ds_priptid = sp.DEVICE AND sp.PART_NAME='PART_A')
),
GetTableId (TBL_ID, DS_ID, Row_Num) as
(select 
 tblid as TBL_ID,
 dsid as DS_ID,
 row_number () over (order by used_bytes desc) as row_num
 from _vt_spu_table_info
),
GetTableName (Table_Name, Obj_Id) as
(select 
 objname as Table_Name, objid as Obj_Id
 from _v_obj_relation_xdb
)
select GetTableName.Table_Name AS TableNameWithMaxUsageInDS,
  GetMaxDSlice.ds_id AS DSWithMaxUsagePCT,
  ds_pct AS PCTMaxForDS
from GetMaxDSlice, GetTableId, GetTableName
where GetMaxDSlice.Row_Num=GetTableId.Row_Num
and GetTableId.TBL_ID=GetTableName.obj_id
and GetMaxDSlice.Row_Num=1;


     TABLENAMEWITHMAXUSAGEINDS      | DSWITHMAXUSAGEPCT | PCTMAXFORDS
------------------------------------+-------------------+-------------
 PREPAID_MSG_SERVICE_USAGE_BKP_0730 |                48 |       49.80

Tuesday, July 8, 2014

Check Netezza Active Sessions/Queries

There are different ways to find active sessions in Netezza, portal or nzsession command line.  But if you like SQL script style command, below is the script:


select  q.qs_sessionid,
        q.qs_planid,
        q.qs_clientid,
        s.dbname,
        s.username,
        s.TYPE SESSION_TYPE,
        s.STATUS SESSION_STATUS,
        s.PRIORITY SESSION_PRIORITY,
        q.qs_cliipaddr,
        q.QS_STATE,
        substr(q.qs_sql,1,200),
        case
            when q.qs_state = 1 then 'pending'
            when q.qs_state = 2 then 'queued'
            when q.qs_state = 3 then 'running'
            when q.qs_state = 4 then 'aborted'
            when q.qs_state = 5 then 'done'
            else 'unknown'
        end QS_STATE_DESCRIPTION,
        q.qs_tsubmit+t.tzoffset qs_tsubmit,
        q.qs_tstart+t.tzoffset tzoffset,
        case when q.qs_tstart = t.EPOCH then 0 else abstime 'now' - q.qs_tstart end running_duration,
        q.qs_pritxt,
        q.qs_estcost,
        q.qs_estdisk,
        q.qs_estmem,
        q.qs_snippets,
        q.qs_cursnipt,
        q.qs_resrows,
        q.qs_resbytes,
        to_timestamp('08JUL14224216','DDMONYYHH24MISS')
from    _v_qrystat q,
        _v_session s,
                _vt_pg_time_offset t
where   q.qs_sessionid = s.id

Sample Output:

 QS_SESSIONID | QS_PLANID | QS_CLIENTID |       DBNAME       |     USERNAME     | SESSION_TYPE | SESSION_STATUS | SESSION_PRIORITY | QS_CLIIPADDR | QS_STATE |                                                                                                  SUBSTR                                                                                                  | QS_STATE_DESCRIPTION |     QS_TSUBMIT      |      TZOFFSET       | RUNNING_DURATION | QS_PRITXT | QS_ESTCOST | QS_ESTDISK | QS_ESTMEM | QS_SNIPPETS | QS_CURSNIPT | QS_RESROWS | QS_RESBYTES
--------------+-----------+-------------+--------------------+------------------+--------------+----------------+------------------+--------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------------------+---------------------+------------------+-----------+------------+------------+-----------+-------------+-------------+------------+-------------
        59376 |     17368 |         112 | CELENR_HLD_PROD_DB | CELEBI_USR_PETL  | sql          | active         |                3 | 10.10.62.15  |        3 | SELECT COUNT(*) FROM ( SELECT CBP_NODE,(((cast(END_SEQ_NUMBER as bigint)-cast(start_seq_number as bigint))+1)-COUNT(*)) AS DIFF FROM HLD_NGIN_ADT_RPT_REC WHERE SEQ_CHECK IS NULL GROUP BY 1,END_SEQ_NUM | running              | 2014-07-09 11:53:37 | 2014-07-09 11:53:37 |                0 | normal    |         26 |          0 |         0 |           2 |           1 |          0 |           0
        56627 |     17288 |         211 | CELENR_HLD_PROD_DB | CELEBI_TEMP_ETL  | sql          | active         |                3 | 10.10.62.15  |        3 | insert into CELENR_HLD_PROD_DB..temp0_final_xfn_MSC select tmp.row_id, ph_calling.electronic_device_type_id calling_electronic_device_type_id, ph_called.electronic_device_type_id called_electronic_dev | running              | 2014-07-09 11:48:20 | 2014-07-09 11:48:21 |              316 | normal    |     150160 |      57811 |     91377 |          19 |          12 |          0 |           0
        58355 |     16828 |         254 | CELEBI_ISD_DB      | CELEBI_USR_08732 | sql-odbc     | active         |                3 | 10.10.47.229 |        3 | SELECT * FROM CELEBI_ZOOM_DB.EBINZADMIN.ZA_POSTPAID_VOICE_2G_3G A LEFT JOIN (SELECT ACCOUNT_ARR_ID,MDN, BILLED_TOTAL_INVOICED_AMOUNT from CELEBI_SOR_PROD_DB..INVOICE X inner JOIN (SELECT SUBSCRIBER_AR | running              | 2014-07-09 11:33:16 | 2014-07-09 11:33:16 |             1221 | normal    |      10621 |      31101 |     25983 |           6 |           6 |   49484266 | 10583460440
        59070 |     17361 |         374 | CELENR_STG_PROD_DB | CELEBI_USR_PETL  | sql          | active         |                3 | 10.10.62.15  |        3 | UPDATE STG_NGIN_CDR_SMS stg SET DESTINATION_SERVICE_PROVIDER_ID=USG.SERVICE_PROVIDER_ID FROM tmp_USAGE_POINT_SP_sms usg WHERE USG.POINT=SUBSTRING(stg.calledpartynumber, 1, 7) AND STG.Serviceflow =1 AN | running              | 2014-07-09 11:53:29 | 2014-07-09 11:53:29 |                8 | normal    |         63 |        202 |         5 |      

Friday, June 27, 2014

nzbackup vs nz_backup

There are 2 commands to perform backup of Netezza.  One is nzbackup.  The other one is nz_backup.  For nzbackup, not much you can play with it except you need to specify the database to backup.  However, nz_backup provides more command options eg, specific table to backup, include where clause etc.  Here's the command options for nz_backup.


Usage:    nz_backup   -dir <dirname>  -format <ascii|binary|gzip>  [ optional args ]
               -or-
          nz_restore  -dir <dirname>  -format <ascii|binary|gzip>  [ optional args ]

Purpose:  To backup (or restore) one or more tables.

          An nz_backup must be run locally (on the NPS host being backed up).

          An nz_restore can be used to restore data into a remote NPS host.  Just
          include the "-host" switch, or set the NZ_HOST environment variable.

          Note:  When doing an "nz_restore -format binary -host <xxx>", the script
                 issues an "nzload -compress true".  This nzload feature only exists
                 in nzload as of NPS 4.6.  If you want to do this on an older version
                 of NPS (4.0 or 4.5) then:
                 o   Install a copy of the 4.6 client toolkit somewhere on your box
                     (it can be used against the older server releases)
                 o   Add it's bin directory to the start of your search PATH
                 o   Then invoke nz_restore

          These scripts can process a single table, multiple tables, or an entire database.

          The data format that is used can be either
               ascii  -- which is very portable.
               binary -- which is the database's compressed external table format.  This
                         is much faster, and results in significantly smaller backup sets.
               gzip   -- ascii, which is gzip'ed on the NPS host.

          The data can be written to (or read from) disk files or named pipes.
               If you use named pipes, another application is used to consume
               (or produce) the data.

          These scripts just concern themselves with the DATA itself.  When backing up
          a table, the DDL is not included.  When restoring a table, the script expects
          the table to already exist.  It will not create it.  It will not truncate it
          (so if the table currently has any data in it, that data will be left untouched
          by this script).

          To backup tables requires the following permissions:

               GRANT SELECT       ON  _VT_HOSTTXMGR         TO <user/group>;
               GRANT SELECT       ON  _VT_HOSTTX_INVISIBLE  TO <user/group>;
               GRANT SELECT       ON  _VT_DBOS_CONNECTION   TO <user/group>;
               --To obtain information about transactions

               GRANT LIST         ON  <DATABASE|dbname>     TO <user/group>;
               --The user must have access to the database that contains the tables

               GRANT SELECT       ON  <TABLE|tablename>     TO <user/group>;
               --The user must have access to the tables themselves, and their data

               GRANT CREATE EXTERNAL TABLE                  TO <user/group>;
               --The user must be able to create external tables, into which the
               --data will be unloaded

          To restore/reload a table requires an additional permission:

               GRANT INSERT       ON  <TABLE|tablename>     TO <user/group>;
               --The user must be able to insert (i.e., reload) data back into the tables

Options:  REQUIRED ARGUMENTS
          ==================

          -dir <dirname> [...]

               The full path to the directory in which the data files will be written
               to (or read from).  This directory must already exist and permit write
               access to it.  The directory name can be as meaningful as you wish to
               make it.

               If you are running this script as some linux user other than 'nz', please
               note that it will actually be one of the 'nz' processes that writes the
               data into this directory.  So linux user 'nz' must also have write access
               to it.  If you are using named pipes (rather than files) then this is the
               directory where the named pipes will be created.

               Examples:
                    -dir /backups/backup_set_17
                    -dir /snap_storage/bu/customer_db/2006_11_18
                    -dir /tmp

               If desired, you may split the backup files up across multiple directories/
               file systems.  Each thread can be associated with a separate "-dir <dirname>"
               by specifying them on the command line.  If you use this feature, then the
               number of directories specified must match the number of threads.

          -format <ascii|binary|gzip>

               Identifies the format to be used for the output files.

               ascii    Universal in nature, but typically results in larger files and
                        slower performance.

               binary   The database's compressed external table format.

               gzip     ascii, which is then compressed (using gzip).  By definition,
                        compressing and decompressing data uses up a lot of CPU cycles (i.e.,
                        it takes a long time).  When using the binary format (compressed/
                        external), the work is done in parallel across all of ths SPUs ... so
                        it is very quick.  But this option uses the NPS host to gzip/gunzip the
                        data.  You will (almost always) want to use multiple threads in order
                        to get more of the host's SMP processors involved in order to speed
                        things along.  The sweet spot seems to be about 8 threads, though you
                        can certainly use a larger/smaller number if you want to break the
                        backup files up into more/fewer pieces.

                                        best      --to--     least
                                       ======     ======     ======
                               Speed:  binary     ascii      gzip
                                Size:  gzip       binary     ascii
                        Universality:  ascii      gzip       binary


          OPTIONAL ARGUMENTS
          ==================

          -h or -? or --help       display this help

          -[rR]ev or -V            print the software revision of this program

          -host <name/IP>          host name or IP address  [NZ_HOST]
          -u    <user>             user name                [NZ_USER]
          -pw   <string>           password                 [NZ_PASSWORD]
          -db   <database>         database name            [NZ_DATABASE]

          -t <tablename> [...]

               # Table(s) within the database to be processed.  If none are specified,
               # then all tables in the database will be processed.
               #
               # If you have a file that contains a list of tablenames to be backed up,
               # (separated by spaces and/or newlines) it can be used via the following
               # syntax:
               #
               #      -t `cat /tmp/the_list_of_tables`

          -threads <nnn>

               # Breaking the backup into multiple threads (per table) can increase the
               # overall thruput, especially for large tables.  This will also have the
               # effect of creating smaller backup files, since each one will now be
               # broken up into multiple pieces.
               #
               # By default, only a single thread will be used.  You can specify a number
               # from 1..31.  Whatever value you specify for the backup must also be used
               # for the restore.  In general, the sweet spot seems to be about 6 threads.

          -script <scriptname>

               # Instead of backing up (or restoring) the data from disk files, you can use
               # named pipes -- allowing another application to consume (or produce) the data
               # on the fly.  To use named pipes, specify that application/script here.  The
               # script will be automatically invoked and passed (as arg 1) the fully rooted
               # pathname of the named pipe that is it supposed to use.
               #
               # For example scripts, see the file(s)
               #      nz_backup.script_example
               #      nz_restore.script_example

          -whereclause <clause>

               # Normally, nz_backup is used to backup the entire contents of a table --
               # all visible rows.  This option allows "you" to tack on a WHERE clause
               # to the data that gets selected ... allowing "you" to backup a subset
               # of the table.  All the power (and responsibility) is put into your
               # hands.  Do wrap the clause in double quotes so it will be passed into the
               # script correctly.  Examples:
               #
               #      -whereclause  "customer_key = 2"
               #      -whereclause  "customer_key in (1,3,5)"
               #      -whereclause  "region_name = 'AMERICA' or region_key = 0"
               #      -whereClause  "order_date between '1998-01-01' and '1998-12-31'"
               #
               # Because this clause gets applied to all tables being backed up, you would
               # probably only want to backup a single table at a time (when using this
               # clause) ... since the clause will typically contain column names that are
               # specific to that table.
               #
               # This clause only applies to backups (not restores).  Its use will be logged
               # in the output of this script (as well as in the pg.log file).

          -ignoreTxID

               # Tables are individually backed up, one at a time.  Since a backup may span
               # many hours, this script insures that the backup represents a consistent
               # point-in-time by using the transaction IDs attached to each row.
               #
               # This switch will override that feature ... and backs up each table with
               # whatever data it contains when the backup (of that particular table) is
               # kicked off.  This insures that your backup will include ALL of the data
               # in a table that has been committed (so you're not susceptible to long
               # running or stale transactions).
               #
               # This switch is primarily of use with NPS 4.0.  In later releases, this
               # script is able to do things differently.

          -dropdata

               # This is for testing purposes only.  As the name implies, the backup will
               # be written to /dev/null, resulting in no backup at all.  This is useful
               # for testing the performance of the NPS components that are involved
               # (SPUs/S-Blades ==> Host), while excluding the speed/overhead of your host
               # storage.

          -sizedata

               # This is for testing purposes only.  Like "-dropdata", but rather than
               # sending the backup data directly to /dev/null it will first be piped
               # thru "wc -c" in order to count the number of bytes in the backup
               # stream (e.g., to provide you with actual sizing information).  So it
               # has the performance characteristics of "-dropdata" ... but provides
               # you additional information.
               #
               # You can use "-format <ascii|binary>" and 1 or multiple "-threads <nn>"
               # when using this switch.
               #
               # Each table will include the following pieces of information
               #
               #      Info:  source table size       80,740,352
               #      Info:  backup file size        76,574,691
               #
               # And summary lines for the entire backup set will display the
               #
               #      TOTAL source table size :  48,267,526,144
               #      TOTAL backup file size  :  46,088,648,210
               #
               # The table size is whatever the table size is -- the amount of storage
               # space it using on disk (as reported by nz_db_size or nz_tables).  This
               # script doesn't know/care if the data is compressed on disk (e.g, CTA0/1/2).
               # Nor does the script know if there are any logically deleted rows in the
               # table (taking up space in the table, but which would not be part of a
               # backup data set).
               #
               # The backup size is the amount of storage that would be required if the
               # backup data set was actually written to disk.  This would represent either
               # the ascii or the binary (compressed external table format) version of the
               # data ... whatever you chose.
               #
               # To get sizing information for a full nzbackup, e.g.
               #      nzbackup   -db DBNAME  -dir /tmp
               # you would use a command line such as this
               #      nz_backup  -db DBNAME  -dir /tmp     -format binary  -sizedata

Outputs:  Status/log/timing information will be sent to standard out ... and will
          include information about any ERROR's that might be encountered.

          Exit status:  0 = success, non-0 = ERROR's were encountered

Examples: $ nz_backup  -format binary -dir /backupdir

          $ nz_restore -format ascii -dir /tmp -db my_db -t table1 -script /tmp/my_script

Comparison:               nzbackup/nzrestore     nz_backup/nz_restore
                          ==================     ====================
                           NPS CLI Utility          Add-on Script
Backup Type
  Full                            X                       X
  -differential                   X
  -cumulative                     X

Granularity
  Entire Database                 X                       X
  Individual Table(s)         nzrestore                   X

Formats Supported
  Compressed Binary               X                       X
  Ascii                                                   X
  Ascii gzip'ed                                           X

Output Destination
  Veritas (NPS 4.0)               X
  Tivoli  (NPS 4.6)               X
  Disk                            X                       X
  Named Pipes                                             X

Multi-Stream Support         nzbackup (6.0)               X

DDL included as part              X              Use the nz_ddl* scripts
  of the backup set

Friday, June 13, 2014

Netezza Versioned Tables

Versioned tables come about as a result of doing an
               ALTER TABLE <tablename> [ADD|DROP] COLUMN ...

          This results in multiple data stores for the table.  When you go to query
          the table, NPS must recombine the separate data stores back into a single
          entity.  This action will be performed automatically and on-the-fly.  But
          it does result in additional query overhead.  Therefore, it is a best
          practice to reconstitute the table as soon as practical by doing a

               GROOM TABLE <tablename> VERSIONS;

          Notes:  The maximum number of table versions allowed is 4 (which means you
                  can perform at most three ALTER TABLE commands before doing a GROOM,
                  since this number includes the original table version itself)


SELECT  the_table.database as "Database",
        MAX
        (   CASE WHEN the_table.objclass = 4910                THEN   SUBSTR(the_table.objname, 3)::nvarchar(128)
                 WHEN the_table.objclass in (4951,4959,4963)   THEN NULL
                 ELSE   the_table.objname::nvarchar(128)
            END
        ) AS "Table Name",
        TO_CHAR ( nvl(SUM ( used_bytes ),0), '999,999,999,999,999,999'  ) AS "Size (Bytes)",
count(distinct(case when objclass in (4959,4963) then objid else null end)) as "# Of Versions"
FROM    _V_OBJ_RELATION_XDB the_table
        left outer join _V_SYS_OBJECT_DSLICE_INFO on
        (
               the_table.objid = _V_SYS_OBJECT_DSLICE_INFO.tblid   and
               _V_SYS_OBJECT_DSLICE_INFO.tblid > 200000
        )
WHERE
            the_table.objclass in (4905,4910,4940,4959,4951,4953,4961,4963)
        and the_table.objid > 200000
GROUP BY
        "Database",
        the_table.visibleid
HAVING  "# Of Versions" >= 2
ORDER BY 1, 2;


Below is sample output:

# Of Versioned Tables         13
     Total # Of Versions      26

       Database        |                       Table Name                       |       Size (Bytes)       | # Of Versions
-----------------------+--------------------------------------------------------+--------------------------+---------------
 CELEBI_BSS_SIT_DDS_DB | SUBSCRIPTION_ACQUISITION_REVENUE_SUMMARY_FULL_11062014 |              566,755,328 |             2
 CELEBI_PREPROD_DDS_DB | COGNOS_CUBE_CONTROL                                    |                2,883,584 |             2
 CELEBI_PREPROD_DDS_DB | COGNOS_CUBE_CONTROL_HIST                               |                  262,144 |             2
 CELEBI_SOR_DB         | REV_GROUP_MASTER                                       |               24,117,248 |             2
 CELEBI_STG_DB         | STG_SAP_CSKU                                           |                  262,144 |             2

Wednesday, June 11, 2014

Check if statistics out of date

You can use query below to check if statistics are outdated.  From nzsql, connect to a database in particular and run the query below:

nzsql -d database <<eof

SELECT
        "Table Name",
        the_label as "Statistics State",
        count(*)  as "# Of Columns",
  TO_CHAR ( _v_table.reltuples, '999,999,999,999,999') AS "Table Rowcount"
FROM
(
SELECT
                _v_relation_column.name as "Table Name",
                CASE WHEN       _v_statistic.recent IS NULL THEN 'not maintained    '
                ELSE
                CASE WHEN       _v_statistic.recent =    85 THEN 'Full              '
                     WHEN       _v_statistic.recent =   341 THEN 'Express           '
                     WHEN       _v_statistic.recent =   149 THEN 'Basic             '
                     WHEN       _v_statistic.recent =     1 THEN 'Full Min/Max OK   '
                     WHEN       _v_statistic.recent =   257 THEN 'Express Min/Max OK'
                     WHEN       _v_statistic.recent =   129 THEN 'Basic Min/Max OK  '
                     WHEN       _v_statistic.recent =     0 THEN 'Full Outdated     '
                     WHEN       _v_statistic.recent =   256 THEN 'Express Outdated  '
                     WHEN       _v_statistic.recent =   128 THEN 'Basic Outdated    '
                     WHEN       _v_statistic.recent =   169 THEN 'Min/Max Only      '
                     WHEN       _v_statistic.recent =   170 THEN 'Unavailable       '
                     WHEN       _v_statistic.recent = 16554 THEN 'Unavailable       '
                     ELSE       _v_statistic.recent || ''
                     END
                END as the_label,
                _v_relation_column.objid
        FROM
                _v_relation_column
                left outer join _v_statistic on
                (   _v_relation_column.name   = _v_statistic.tablename   AND
                    _v_relation_column.attnum = _v_statistic.attnum
                )
        WHERE
                    _v_relation_column.objid > 200000
                AND upper(_v_relation_column.type) in ('TABLE','SECURE TABLE')
) the_subquery
 LEFT OUTER JOIN _v_table ON the_subquery."Table Name"  =  _v_table.tablename
WHERE trim(the_label) NOT IN ('not maintained','Full','Express','Basic','Unavailable')

GROUP BY "Table Name", "Statistics State", "Table Rowcount"
  ORDER BY "Table Name", "Statistics State";

eof