in my last post i went deep on how to quickly clean pobs data from a primavera p6 xer 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 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.
so the next question is…
“how is pobs data affecting primavera’s performance?”
we all want our copies and versions of primavera p6 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.
let’s dig into this issue of pobs data and p6’s performance then.
pobs data & primavera p6 performance
a bit of review first at how pobs is getting around.
the pobs data cycle looks something this:
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. and as we export projects to xer files, a copy of some of that 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.
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 as my client reported. this is pretty much unacceptable.
does the data in the pobs table affect overall performance of p6?
i don’t know. since we don’t really know how the pobs gets generated in the first place.
sure we know that it gets passed around during import / export – and can accumulate. but what is the original source?
more to come on this soon i hope…
before you start
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.
how to check your pobs table data in ms sql using the command-line
if you’re using microsoft sql express 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. in case you don’t have sql server management studio, you can still dig in using the sqlcmd and the command line.
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.
1) open a command prompt in windows.
2) login to your sql express database engine with the following command:
sqlcmd -s server1\sqlexpress -u sqluseraccount -p sqlpassword
on my system, this looks something like this:
sqlcmd -s localhost\sqlexpress -u sa -p prima123vera
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.
3) get a listing of databases on your system
if your login was successful, you should see a prompt that looks like “1>”. that means you’re in.
let’s get a listing of all of the databases in your install. type the following at the prompt:
select name from sys.databases
go
these are on separate lines. you should see a list of all of your databases. now let’s pick one.
4) set a database to use
once you’ve identified your database name, you want to tell sql that you’ll be working inside that db. type this:
use my_db_name_here
go
here’s what you should get in response.
5) query the pobs table
to see what data is in the pobs table, let’s run this query:
select * from pobs
go
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.
scroll down for how to clean the pobs table.
how to clean your pobs table data in ms sql using the command-line
ok, once you’re this far, the cleaning part is easy. simply run the following command
delete from pobs
go
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.
how to check your pobs table in ms sql using management studio
if you’ve got microsoft sql management studio or something similar, then follow these steps to clean out your pobs table.
1) login to microsoft sql management studio
make sure to login as the “sa” user using sql server authentication.
2) find the pobs table
start by finding your database in the object explorer window.
expand the tables sub-entry and scroll down until your find “dbo.pobs” which is the pobs table. highlight the table and click the “new query” button on the toolbar at the top of the screen.
3) check for entries
type the following in the query window:
select * from pobs
click the “! execute” button on the toolbar.
you might an empty table or you might see a result like this:
how to clean your pobs table data in ms sql using management studio
ok. if you have data, then let’s delete everything from the entire table. it’s a simple command. type:
delete from pobs
click execute.
how to check your pobs table data in oracle xe
if you’re using oracle database express edition 10g, aka oracle xe, 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.
1) open the database home page
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.
in this case, you can login with “admuser”. again hopefully you know the password.
2) run the sql command.
from here, you’ll want to access the menu on the large sql icon and choose “sql commands -> enter command“.
once you’ve got a blank command-entry screen loaded, you’ll type in the following:
select * from pobs
then click the run 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.
how to clean your pobs table data in oracle xe
in oracle xe, deleting entries from the table is very straightforward. in the sql command window, type in the following:
delete from pobs
your pobs table should now be empty.
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.
alter trigger rt_pobs disable
alter trigger rt_pobs_del disable
it’s pretty easy, and now it’s done.
is deleting pobs data safe?
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)“. since pobs data is not used inside the application, it’s safe to delete it.
conclusion
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.
will this fix all your pobs problems for good?
probably not. since pobs data is imported into your database when you import xers files that contain pobs data, 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.
can you automate cleaning the table?
yes. for any of these database platforms, you could create a script that could be triggered to run periodically by the windows scheduler to run the delete command and clean the table. if you want me to write-up that procedure, leave me a comment below and i’ll gladly tackle it if there’s interest.
for now, keep on planning and hoping our software gets fixed soon.
