Fetch the *entire* policy content with SQL

[FrankMortensen]

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

[Andres Diaz]

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

[FrankMortensen]

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

[andreask]

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