Setting a version number to procedures and functions

[former_member329524]

Hello, all

I need to help the technical support department in my company to identify db objects, which require updating from the central location.

In order to do that I need some point of reference, either a version number or a last modified time stamp for each DB object.

Is there such a property for functions, procedures and triggers? Using comments is out of question, this property is already utilized.

I am using SQL Anywhere 16.

Thank you

Arcady

[chris_keating]

We do record the creation time of objects but not when objects have been modified.

[former_member186998]

Hi Arcady,

Can you confirm following KBA?

2055683 - How to provide SAP Support with some details of a SQL Anywhere database using dbtsinfo

Can this information help you?

Thanks,

Atsushi

[former_member329524]

I am sorry.

I have googled it and got nothing.

Can you provide a direct link to the article, please?

[former_member186998]

Hi Arcady,

https://launchpad.support.sap.com/#/notes/2055683

Thanks,

Atsushi

[former_member329524]

I am sorry,

I failed to access the document after numerous login attempts (which the site never said that failed).

I was staring at the "waiting ..." page for 5 minutes and then nothing.

Is there another way to get this document?

[former_member186998]

Hi Arcady,

Sorry...

Can you confirm this information in the following procedures?

1. https://support.sap.com/

2. Enter Note/KBA Number -> 2055683

P.S.

You can use the following command.

dbtsinfo -c "<connection string>" -o c:\test\info.txt

In this case, the info.txt file has the information of the database.

Thanks,

Atsushi

[former_member329524]

Thank you,

I read the document.

But this is not at all what I need.

I do not need the version of engine. I need to mark individual stored procedures and functions (and triggers) with a version number of a last modified time stamp.

Is it at all possible to do it? For example, is it possible to log changes in the stored procedure script to a specific table?

[former_member182948]

Hi Arcady,

You might be able to use the source control.

Please see below.

"Source control integration in Interactive SQL"

http://dcx.sap.com/index.html#sa160/en/dbadmin/da-guitools-s-4994493.html

"Stored procedures, triggers, batches, and user-defined functions"

http://dcx.sap.com/index.html#sa160/en/dbusage/ug-proctrig.html

Source control
Use source control software to track changes to source code, and changes to objects created from source (including stored procedures), that you deploy to the database.

Regards,

Koichi

[former_member329524]

Thank you, Koichi

I have source control, I know exactly which changes were made on the development servers and when.

My problem is propagating the changes to the hundreds of customer locations. Each customer DB has hundreds of individual objects (triggers, functions, SPs), and each of them may have a different version. I need to know (without running full DB compare) which procedures need to be updated on client location. In order to this, I need some sort of DDL tracking on the customer DB.

Question: how do I track DDL changes in a SQL Anywhere DB? Preferrably, using nothing but the DB itself.

[former_member182948]

Hi Arcady,

The procedures&functions and triggers are stored in the system table.

However, you can't see it.

SQL Anywhere provide system view to a substitute.

"SYSPROCEDURE system view"

http://dcx.sap.com/index.html#sa160/en/dbreference/sysprocedure-system-view.html

"SYSTRIGGER system view"

http://dcx.sap.com/index.html#sa160/en/dbreference/systrigger-system-view.html

Unfortunately, there doesn't seem to be the history about the update there.

So You will not be able to keep track of the updates from the object on the database.

By the way,

SQL Anywhere has auditing function.

Auditing can collect only DDL changes.

It might be useful to you.

Please see below.

"Database activity audits"

http://dcx.sap.com/index.html#sa160/en/dbadmin/auditing-security.html

Regards,

Koichi

[former_member329524]

Thank you, Koichi

Regarding the auditing function: is there a way to retrieve the audit data to a relational table?

How can one read it from a program?

[former_member182948]

Hi Arcady,

*Note*

The auditing feature is not enabled by default.

Accordingly the data doesn't exist currently.

>Regarding the auditing function: is there a way to retrieve the audit data to a relational table?

Unfortunately, probably not.

>How can one read it from a program?

Please see below about how to retrieving audit.

"Retrieving auditing information (dbtran utility)"

http://dcx.sap.com/index.html#sa160/en/dbadmin/retrieving-auditing-security-dbtran.html

"Auditing examples"

http://dcx.sap.com/index.html#sa160/en/dbadmin/example-auditing-security.html

Regards,

Koichi