Highlighted
Acclaimed Contributor.. Acclaimed Contributor..
Acclaimed Contributor..
120 views

Fetch the *entire* policy content with SQL

Hi,

OBM 2019.05 Classic with a remote MS SQL DB.

We every now and then fetch the content of certain OBM policies by means of SQL queries towards the cfg_templatefile table in the event DB. The content of each policy is stored as datatype "Image" in the database. We therefore CONVERT it to VARCHAR when fetching it.

 

This works great, provided that the content of the policy (in RAW format) is less than 8000 bytes. As soon as we try to convert the content to something bigger (n) than 8000 the conversion fails with the error message "The size (n) given to the type 'binary' exceeds the maximum allowed for any data type (8000)."

My question is; How can we fetch the entire content of even big policies directly from the OBM database?

Cheers,
Frank

 

0 Likes
3 Replies
Highlighted
Micro Focus Contributor
Micro Focus Contributor

What are you trying to do are direct queries from the DB? If so I am sorry to tell you that this is not supported

I have searched for a tool to be able to do this in OMI but I did not find any, for the moment what I suggest is that you try to optimize the data of the policies so that they are not so heavy while I find something that can be useful to you

0 Likes
Highlighted
Acclaimed Contributor.. Acclaimed Contributor..
Acclaimed Contributor..

Hi,

Thanks for your reply! Yes, I am talking about direct queries towards the DB. It's not supported, I know, but it is fully possible and harmless, and that's why I would like to hear about other users' experiences. We would certainly never make any changes to DB-tables or any content, but alot of interesting information can be fetched from the DB.

If you find something useful, I would be most grateful!

Cheers,
Frank

0 Likes
Highlighted
Acclaimed Contributor.. Acclaimed Contributor..
Acclaimed Contributor..

Hallo Frank,

cant help you with DB issue but i use a small script to backup all policies and versions. May be this is useful for your case. It need a RC File to work. I am sure you know how to create one with opr-node -rc_file -set_rc ...

Its a dirty hack but works.

set -x
#env


WORKDIR=/opt/TYB/Policy_Download
RC_FILE=$WORKDIR/rc_file
DOWNDIR=$WORKDIR/configdownload
LIST=$WORKDIR/list_of_all_policies

# Flat true = all polices in one folder
# Falt false = for each policy is one folder created
FLAT=FALSE

# Allversion true = download all version of a policy
# Allversion false = download only latest version of a policy
ALLVERSION=FALSE

# download list of all policies
opr-template -rc_file $RC_FILE -list > $LIST

while read LINE ; do
{
POLNAME=`echo $LINE | cut -f2 -d \"`
DIRNAME=`echo $POLNAME | tr " " "_"`
if [ $FLAT = TRUE ]; then
DIRNAME=""
fi
if [ $ALLVERSION = TRUE ]; then
OA="-all"
fi
mkdir -p $DOWNDIR/$DIRNAME
opr-template -rc_file $RC_FILE -download -name "$POLNAME" $OA -output $DOWNDIR/$DIRNAME

}
done < $LIST

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.