Monday, March 28, 2011

Peopletools 8.51 : on TABLE’s build (bis)

This blog entry continues the previous one I wrote about table’s build, especially about the partitioned tables. As it has been showed in the previous article, Peopletools 8.51 takes advantages of the DBMS_METADATA.GET_DDL package from the Oracle database, very useful if you are working within partitioned tables. Those are saved and survive a table rebuild through AppDesigner which has never been the case before.
Is it really right ? Does it really survive a table’s rebuild ? Well, I showed in the previous article that it does, but I came across a case which does not ! Example.

If you have to run the SETSPACE.SQR to update the tablespaces’ list in Peoplesoft, you’ll come in troubles within the partitioned tables.

Still working with my partitioned table PS_MYTABLE : 
TableDDL_013
Now, I run SETSPACE.SQR : 
TableDDL_012
Surprisingly, it updated the row of my table PS_MYTABLE to PTTBL (which has actually nothing to do with my table!).
Now from AppDesigner, the tablespace of this record has been changed : 
TableDDL_014
And if I rebuild the table, the partitions are simply lost, the table going to PTTBL ! 
TableDDL_015
To retrieve the partitions, put back the table in the original tablespace CUAUDIT, and rebuild : 
TableDDL_016  
TableDDL_017
Pfew ! So, if the partitions are much better managed from AppDesigner table’s build than before, there’s still few surprise and thing to know !

So, working with partitions ? Take care, Peopletools 8.51 does not solve all, partitioned tables are still a very sensitive domain in Peoplesoft world !

No comments: