I have a db2 table that contains customer transactions.
This table is about 500 Million records and when I try to run a select query BETWEEN specific dates the query runs for a long time. A DBA in the company told me that it would run faster if i could specify the partition in my SQL.
So the question is: Provided I have the following table whose name is Cust_Trans With the following columns
Cust_Num, Trans_Date, Trans_Type, Trans_Value + 30 more columns
What changes can I make to the following SQL query so it will target a specific partition?
Select * From Cust_Trans
Searching the forum and IBM documentation I found solutions that involve SYSCAT schema and datapartitionnum function
However my DB2 version has no SYSCAT schema but only SYSIBM schema. So i could not implement any of the solutions
Advertisement
Answer
For Db2 for Z/OS, the catalog tables are in SYSIBM schema.
For Db2 for Linux/Unix/Windows, the catalog views are in SYSCAT schema.
For Db2 for i-series the catalog may be in QSYS2 schema.
For Db2 for Z/OS, Look at SYSIBM.SYSCOLUMNS for you table, the columns whose PARTKEY_COLSEQ have non-zero are the range-partitioning columns.
The suggestion is to use the partitioning column(s) in the WHERE clause of the query, presuming that at least one of the partition column(s) is the trans_date column that you mention.