IBM Support

Old data at agent not inserted to warehouse partitions

Technical Blog Post


Abstract

Old data at agent not inserted to warehouse partitions

Body

image

Old data at agent not inserted to warehouse partitions

The errors seen when this happen are:

CTX_ODBCError(20)

SQL0327N The row cannot be inserted into table "XXX.XXX" because it is outside the bounds of the defined data partition ranges. SQLSTATE=22525

These errors occur when the data that needs to be inserted is outside of the range of the defined partitions.
 
The S&P agent is responsible for creating the partitions but if it has an error, or is not running correctly the partitions might not be created and errors can be seen in the agent and WPA logs on the insert of data.

Often what is needed is to get the S&P agent running correctly and so creating the current partitions needed for the data to be inserted.
A review of the S&P *java* logs show any errors when partitions are being created and these might need reviewed with a database administrator if there are DB2 or Oracle issues.

However in this case, this was only happening for one agent type, and the current partitions were being added correctly to the database.

Further investigation found that there was one particular attribute field for this agent that was not being saved correctly, and the agent had historical data collected for months that had not been put in the warehouse.
It was not that the data was too large to be input as that would produce a different error.
This data was important so the historical collection could not just be cleared, the data had to be inserted into the warehouse.  

The issue was the MV partition for the attribute did not exist.
 When the partitions are created by the S&P agent they are created for 10 days (default value) from the date of creation and one partition with the name PYYYYMMDD_MV is created to take any data that is not within the dates of those partitions.

There is a pdf document to download that gives information on checking what partitions are set up:

https://www.ibm.com/developerworks/community/wikis/form/anonymous/api/wiki/6da0e584-8f55-42cc-8377-ba7d2182f3d9/page/1287fc75-992a-4195-bb2e-043734fb9a94/attachment/7ba577d1-968a-4d3f-87f3-12787bcff785/media/TDW-how-to-check-partitioning-status.pdf

An sql query like (this one is for DB2) will give an output of the partitions in the table space.

 SELECT    D.SEQNO,    D.TABNAME,    D.DATAPARTITIONNAME,    T.TBSPACE FROM    SYSCAT.DATAPARTITIONS D,   SYSCAT.TABLESPACES T   WHERE    D.TABSCHEMA = '<schema name>' AND   D.TABNAME='<table name>' AND   D.TBSPACEID= T.TBSPACEID  ORDER BY SEQNO;

Here is an example of how to create the *_MV  partition for KLZ_Disk :

 Example of creation of the Minimum Value (_MV)  Partition for the Raw Table KLZ_Disk when the oldest data (WRITETIME) to be contained in the table is older than Mar 13 23:59:59.999.

ALTER TABLE "ITMUSER"."KLZ_Disk" ADD PARTITION P20170313_MV STARTING MINVALUE  INCLUSIVE ENDING '1170313235959999' INCLUSIVE  IN USERSPACE1  INDEX IN USERSPACE1

 Where DB2INST1  The DB2 Schema Name (User)                              

      KLZ_Disk  The Raw Table Name                                      

      P20170313_MV The Minimum Partition name for Mar 13, 2017          

      MINVALUE  The Starting Value for this Partition  
(this is the catch-all so anything earlier than the Ending Value of Mar 13 23:59:59.999 will be thrown into this Partition)                

      1170313235959999  The Ending Value Mar 13 23:59:59.999            

                                                                        
So rows that have a WRITETIME Column value earlier than 1170313235959999 will be inserted into this partition by DB2.                  

Once the minimum value partition is created, the data is inserted into the database and then summarized in the normal running manner.


 

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"","label":""},"Component":"","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSTFXA","label":"Tivoli Monitoring"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

UID

ibm11083285