{"id":31686,"date":"2015-08-13t22:46:02","date_gmt":"2015-08-14t02:46:02","guid":{"rendered":"\/\/www.deco-dalles.com\/?p=31686"},"modified":"2019-10-18t09:51:05","modified_gmt":"2019-10-18t13:51:05","slug":"clean-p6-pobs-table","status":"publish","type":"post","link":"\/\/www.deco-dalles.com\/clean-p6-pobs-table\/","title":{"rendered":"how to clean your p6 pobs table on mssql & oracle db"},"content":{"rendered":"
in my last post i went deep on how to quickly clean pobs data from a primavera p6 xer<\/a> file using a simple text editor. and we discovered that removing pobs data from an xer file greatly improved the import time for large xer files. a big thanks to readers who left comments<\/a> on that post letting me and you know that there are some 3rd party tools (free and paid) that handle this in a more automated fashion which is good to know.<\/p>\n so the next question is…<\/p>\n “how is pobs data affecting primavera’s performance?”<\/p>\n we all want our copies and versions of primavera p6<\/a> to hum along and perform well – it’s a given. slow software sucks. especially given how most p6’ers (that’s a new term i’m trying out…see if it sticks ;-] ) are power users and really put the software through some paces. when you’re working with huge complex schedules and deadlines loom, every second waiting for the software better be absolutely necessary.<\/p>\n let’s dig into this issue of pobs data and p6’s performance then.<\/p>\n a bit of review first at how pobs is getting around.<\/p>\n the pobs data cycle looks something this:<\/p>\n we know that when a user imports an xer file that has pobs entries in it, that data populates in our primavera p6 database in the pobs table. so the more files we import that contain pobs data, the larger that pobs table will grow.\u00a0and as we export projects to xer files, a copy of some of\u00a0that pobs data moves to the file, and ends up in another primavera p6 database. eventually, as we’re finding out now, a large pobs table can have an impact on the performance of primavera p6.<\/p>\n we already know it has an impact on exporting xer files, which takes longer. and importing an xer file with a lot of pobs data can literally take hours<\/strong> as my client reported<\/a>. this is pretty much unacceptable.<\/p>\n does the data in the pobs table affect overall performance of p6?<\/p>\n i don’t know. since we don’t really know how the pobs gets generated in the first place.<\/p>\n sure we know that it gets passed around during import \/ export – and can accumulate. but what is the original source?<\/p>\n more to come on this soon i hope…<\/p>\n i highly recommend you make a backup of your database prior to running any sql commands. if you break something, you’ll want to have a contingency plan.<\/p>\n if you’re using microsoft sql express<\/a> as your primavera database engine, then you might have also installed the handy microsoft sql server management studio to manage your databases. if you did, you should see it in your start->all programs list in windows. \u00a0in case you don’t have sql server management studio, you can still dig in using the sqlcmd and the command line.<\/p>\n what we want to do here is to check the pobs table in our primavera database for entries. if it’s empty, good. if it’s not, then we’ll clean it up.<\/p>\n 1) open a command prompt in windows.<\/strong><\/p>\n <\/p>\n 2) login to your sql express database engine with the following command:<\/strong><\/p>\n on my system, this looks something like this:<\/p>\n it’s best to login with the “sa” user – hopefully you know the password. you might try “prima123vera” if you don’t – it was used in a lot in install guides.<\/p>\n 3) get a listing of databases on your system<\/strong><\/p>\n if your login was successful, you should see a prompt that looks like “1>”. that means you’re in.<\/p>\n let’s get a listing of all of the databases in your install. type the following at the prompt:<\/p>\n these are on separate lines. you should see a list of all of your databases. now let’s pick one.<\/p>\n 4) set a database to use<\/strong><\/p>\n once you’ve identified your database name, you want to tell sql that you’ll be working inside that db. type this:<\/p>\n here’s what you should get in response.<\/p>\n <\/p>\n 5) query the pobs table<\/strong><\/p>\n to see what data is in the pobs table, let’s run this query:<\/p>\n if your result was more that 0 rows affected, like mine here, then you’ve got some data in the pobs table that will be affecting your import \/ export performance.<\/p>\n <\/p>\n scroll down for how to clean the pobs table.<\/p>\n ok, once you’re this far, the cleaning part is easy. simply run the following command<\/p>\n that’s it. you’re done and you pobs table is now empty. make sure to read the recommendations at the end of this article.<\/p>\n <\/p>\n if you’ve got microsoft sql management studio or something similar, then follow these steps to clean out your pobs table.<\/p>\n 1) login to microsoft sql management studio<\/strong><\/p>\n make sure to login as the “sa” user using sql server authentication.<\/p>\n <\/p>\n 2) find\u00a0the pobs table<\/strong><\/p>\n start by finding your database in the object explorer window.<\/p>\n expand the tables<\/strong> sub-entry and scroll down until your find “dbo.pobs<\/strong>” which is the pobs table. highlight the table and click the “new query<\/strong>” button on the toolbar at the top of the screen.<\/p>\n 3)\u00a0check for entries<\/strong><\/p>\n type the following in the query window:<\/p>\n click the “!\u00a0execute<\/strong>” button on the toolbar.<\/p>\n you might an empty table or you might see a result like this:<\/p>\n <\/p>\n ok. if you have data, then let’s delete everything from the entire table. it’s a simple command. type:<\/p>\n click execute.<\/p>\n <\/p>\n if you’re using oracle database express edition 10g, aka oracle xe<\/a>, the free database that ships with primavera p6 version 8.x, then you’ll need to follow these steps to check and clean your pobs table.<\/p>\n 1) open the database home page<\/strong><\/p>\n some versions may differ, but i have a windows program entry in my start menu called “goto database home page” under the oracle database express edition 10g folder. once you get the home page up in a browser, you’ll need to login with the appropriate user to access the proper tables.<\/p>\n in this case, you can login with “admuser”. again hopefully you know the password.<\/p>\n <\/p>\n 2) run the sql command.<\/strong><\/p>\n from here, you’ll want to access the menu on the large sql icon and choose “sql\u00a0commands -> enter command<\/strong>“.<\/p>\n <\/p>\n once you’ve got a blank command-entry screen loaded, you’ll type in the following:<\/p>\n then click the run<\/strong> button. you should now know if your pobs table has any data in it or not. if it does, continue on to the next section.<\/p>\n in oracle xe, deleting entries from the table is very straightforward. in the sql command window, type in the following:<\/p>\n your pobs table should now be empty.<\/p>\n however, with oracle xe, it’s also necessary to disable 2 triggers by running the following sql commands – as per oracle support. run each one separately, not together, as you did the delete command.<\/p>\n it’s pretty easy, and now it’s done.<\/p>\n as far as we know, yes it is safe. in fact, this procedure has been recommended by oracle support in a document titled “pobs table has grown in size and affects performance with project import\/export (doc id 1967569.1)<\/a>“. since pobs data is not used inside the application, it’s safe to delete it.<\/p>\n i’ve done my best to outline how to safely clean your primavera p6 database regardless of which database engine you are using. i highly recommend you have a backup in place before you run any sql commands.<\/p>\n will this fix all your pobs problems for good?<\/p>\n probably not. since pobs data is imported into your database when you import xers files that contain pobs data<\/a>, your database may continue to accumulate pobs data. i recommend you use the scripts and processes i’ve outlined to monitor your pobs table on a monthly basis. then clean as needed.<\/p>\n can you automate cleaning the table?<\/strong><\/p>\npobs data &\u00a0<\/strong>primavera p6 performance<\/h2>\n
before you start<\/h2>\n
how to check your pobs table data in ms sql using the command-line<\/h2>\n
sqlcmd -s server1\\sqlexpress -u sqluseraccount -p sqlpassword<\/code><\/p>\n
sqlcmd -s localhost\\sqlexpress -u sa -p prima123vera<\/code><\/p>\n
select name from sys.databases
\ngo<\/code><\/p>\nuse my_db_name_here
\ngo<\/code><\/p>\nselect * from pobs
\ngo<\/code><\/p>\nhow to clean\u00a0your pobs table data in ms sql using the command-line<\/h2>\n
delete from pobs
\ngo<\/code><\/p>\n
\nhow to check your pobs table in ms sql using management studio<\/h2>\n
select * from pobs<\/code><\/p>\n
how to clean\u00a0your pobs table data in ms sql using management studio<\/h2>\n
delete from pobs<\/code><\/p>\n
how to check your pobs table data in oracle xe<\/h2>\n
select * from pobs<\/code><\/p>\n
how to clean\u00a0your pobs table data in oracle xe<\/h2>\n
delete from pobs<\/code><\/p>\n
alter trigger rt_pobs disable<\/code><\/p>\n
alter trigger rt_pobs_del disable<\/code><\/p>\n
is deleting pobs data safe?<\/h2>\n
conclusion<\/h2>\n