0

I am trying to select all data out of the same specific table partition for 100+ tables using the DB2 EXPORT utility. The partition name is constant across all of my partitioned tables, which makes this method more advantageous than using some other possible methods.

I cannot detach the partitions as they are in a production environment.

In order to script this for semi-automation, I need to be able to run the query:

SELECT * FROM MYTABLE 
WHERE PARTITION_NAME = MYPARTITION;

I am not able to find the correct syntax for utilizing this type of logic in my SELECT statement passed to the EXPORT utility.

J. Williams
  • 3
  • 5

1 Answers1

1

You can do something like this by looking up the partition number first:

SELECT SEQNO 
FROM SYSCAT.DATAPARTITIONS
WHERE TABNAME = 'YOURTABLE' AND DATAPARTITIONNAME = 'WHATEVER'

then using the SEQNO value in the query:

SELECT * FROM MYTABLE 
WHERE DATAPARTITIONNUM(anycolumn) = <SEQNO value>

Edit:

Since it does not matter what column you reference in DATAPARTITIONNUM(), and since each table is guaranteed to have at least one column, you can automatically generate queries by joining SYSCAT.DATAPARTITIONS and SYSCAT.COLUMNS:

select 
  'select * from', p.tabname, 
  'where datapartitionnum(', colname, ') = ', seqno 
from syscat.datapartitions p 
inner join syscat.columns c 
  on p.tabschema = c.tabschema and p.tabname = c.tabname
where colno = 1 
and   datapartitionname = '<your partition name>' 
and   p.tabname in (<your table list>)

However, building dependency on database metadata into your application is, in my view, not very reliable. You can simply specify the appropriate partitioning key range to extract the data, which will be as efficient.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
  • Thank you for this solution. I had been heading down this path this morning but did not put it together yet. – J. Williams Feb 16 '16 at 19:44
  • I will only be doing this action once, scripted, so I'll be doing some data validation to ensure everything is intact by this method. I'm still trying to find a way to let (anycolumn) be a variable in my script. DATAPARTITIONNUM (1) is not working as an argument. – J. Williams Feb 16 '16 at 19:46