XMETA table OMDMODEL_JOBPARAMETER

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

XMETA table OMDMODEL_JOBPARAMETER

Post by qt_ky »

Our XMETA repository has ballooned to 100 GB. We suspect the OMD tables are consuming much of the space. The OMDMODEL_JOBPARAMETER table has over 60 million records.

The "Generate operational metadata" setting in Administrator is turned off for each project, however it was enabled in the past on several projects. Is this the setting that is directly responsible for growth of the OMD tables?

Are there other settings that can also cause OMD table growth?

Despite the project setting being disabled, we continue to see OMD tables' record counts increase. After disabling the setting at the project level, must the jobs be recompiled and/or rescheduled to effectively disable generating OMD? I could not find anything in the IBM Knowledge Center that suggested those actions.
Choose a job you love, and you will never have to work a day in your life. - Confucius
JRodriguez
Premium Member
Premium Member
Posts: 425
Joined: Sat Nov 19, 2005 9:26 am
Location: New York City
Contact:

Post by JRodriguez »

If the "Generate operational metadata" was enabled also at the job level you will need to disable it and recompile

You could see exactly which jobs are generating metadata, the xml files are named after the DataStage jobs that generates the files, and by default are place in below directory

/opt/IBM/InformationServer/Server/DSOMD/xml

In the meant time if you just want to avoid ingesting the files into the XMETA, just stop the process

IBM/InformationServer/Server/DSOMD/bin
DSOMDMonApp.sh -stop
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

I have double-checked that the "Generate operational metadata" setting in Administrator is turned off for each project.

Then I ran the DSOMDMonApp.sh -stop command as suggested.

I immediately found new xml files accumulating. Withing 24 hours there are over 2000 new xml files.

I think this confirms that the documentation on this setting is not telling the whole story.
Choose a job you love, and you will never have to work a day in your life. - Confucius
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Thank you Julio for responding. I found that the jobs must be rescheduled to effectively disable generating new OMD xml files. I have confirmed that without recompiling any jobs.

I would mark this resolved but it's still not clear how to reclaim the repository space. We have run dozens of purge commands and not found any relief so we have opened a PMR about it.
Choose a job you love, and you will never have to work a day in your life. - Confucius
JRodriguez
Premium Member
Premium Member
Posts: 425
Joined: Sat Nov 19, 2005 9:26 am
Location: New York City
Contact:

Post by JRodriguez »

Did you try the workbench purgeOMD command? This istool command allow you to purge the operational metadata by date range, number of record to keep, engine, etc. The id running the command would need to have metadata admin rights.

Do you have IGC installed and Governance people using it in your site? Are you running the delete command from your services tier? It might just be that the command only work from where the IGC was installed (services tier) the istool framework for IGC needs to be installed....try from the client side

If your Governance business users are not using this data, I would just carefully (backup and approved first) truncate the tables...the data is only used on IGC and will depend on how.much time back they need to report on it
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Rescheduling jobs has helped stop the bleeding!

We have all the products in enterprise edition installed with all server tiers on one system. Our IGC usage is not yet that mature. I can safely purge OMD.

I have used the workbench purgeOMD command and experimented with its timeout setting by adding it to a properties file. Also tried various date ranges and found that the only way to consistently avoid timeouts is to purge a single date at a time. It tends to always report success ("data has been purged") yet the record counts have remained the same before and after purging.
Choose a job you love, and you will never have to work a day in your life. - Confucius
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Love those... "I have successfully done - nothing!" :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Yes, I was referring to the iis.client.site.properties file as detailed in this tech note:

Istool command fails with "java.net.SocketTimeoutException: Read timed out"
http://www-01.ibm.com/support/docview.w ... wg21964918

We have confirmed through checking record counts before and after running the purge commands, that the purge commands can have unexpected results. Sometimes it simply does not purge all the OMD records within the specified date range. We are asking Support about this behavior on a PMR.
Choose a job you love, and you will never have to work a day in your life. - Confucius
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

We have discovered that the purgeOMD command is not exactly date-based or date-inclusive as implied on the Knowledge Center web page.

https://www.ibm.com/support/knowledgece ... e_OMD.html

It takes your date range and converts it to a timestamp range (in unix epoch format). So if you tell it to purge Jan 3, 2017 through Jan 3, 2017 (inclusive) I thought it would have purged all Jan 3, 2017 OMD records. What it effectively does is to say purge where start >= Jan 3, 2017 00:00:00 and end <= Jan 3, 2017 00:00:00.

We had arrived at purging a single day at a time in this manner because a range like Jan 3, 2017 through Jan 4, 2017 (which turns out to be a single day or 24 hour period) was giving timeout errors.
Choose a job you love, and you will never have to work a day in your life. - Confucius
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

I am marking this as a workaround because it's still not resolved. We have to purge one day at a time, which will take a very, very long time to complete. We also still have a PMR open and have requested a manual purge process.
Choose a job you love, and you will never have to work a day in your life. - Confucius
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Now marking this resolved as we had a web session with IBM support. Bottom line for now is that you have to go through Support to get is resolved.

There is a temporary workaround that requires support to provide some files from a newer release, modify a script, run a new command, then put everything back the way it was. It turns out there is a a supported purge command post 11.5.x governance rollup 6.
Choose a job you love, and you will never have to work a day in your life. - Confucius
skathaitrooney
Participant
Posts: 103
Joined: Tue Jan 06, 2015 4:30 am

Post by skathaitrooney »

Can i ask you which script did you run. Did you manage to purge the OperationalMetadata all at once somehow, xmetaAdmin ? I am really interested in the manual purge process
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

The command and the options we ran it with essentially had the effect of immediately truncating several related XMETA database tables at once, so use with great care and caution...

./xmetaAdmin.sh deleteOneModel -dbfile ../conf/database.properties -model OMDModel

Again, if you're on an older version like we are, then you need to work with Support, as we had to download some newer jar files, and edit at least one script before the above option became available to us.
Choose a job you love, and you will never have to work a day in your life. - Confucius
skathaitrooney
Participant
Posts: 103
Joined: Tue Jan 06, 2015 4:30 am

Post by skathaitrooney »

Thanks a lot.


I got the same guidelines from Support, we are already on 11.5. The command works like a charm. All OMD cleaned for me. XMETA size dropped from 60 GB to mere 17 GB.
Post Reply