We recently purchased the DB Audit central repository bundle and 10-server license and have been setting it up to work with our database estate (MS SQL 2000/2005, DB2 v9.1 LUW and Sybase ASE 11.9.2).

We are first configuring it all to run in our test environment, to give us a feel of how everything hangs together before rolling it out to production. We have had success in setting up local audit databases for each of our database systems, however, when we setup the automatic archiving to the repository database (hosted on an instance of MS SQL 2005), via a DB Audit Alert Center job for our Sybase ASE 11.9.2, we get an error in the job log when using the data pump method, as below:

Code:

Connecting to Alert Center...

******************************************************
* Copyright (C) 2000-2007 SoftTree Technologies, Inc.*
******************************************************
* 24x7 Scheduler Java API v4.1. *
******************************************************
* Unauthorized use or distribution of this program *
* may result in severe civil and criminal penalties, *
* and will be prosecuted to the maximum extent *
* possible under the law. *
******************************************************

I can see that the column names and data types are the same in both the sybsecurity.dbo.sysaudits_01 table (local audit database for Sybase) and the repository staging table db_audit.STAGE$300001

We are using the 'data pump' method to get the data across, as we cannot use linked servers due to the repository database being hosted on x64 and sybase 11.9.2 is on x86 and there aren't any Sybase x64 ODBC drivers for our old version of Sybase (without going down the expensive third-party route). The BCP method also fails, with a 'The connection is closed' error as below:

Code:

Connecting to Alert Center...

******************************************************
* Copyright (C) 2000-2007 SoftTree Technologies, Inc.*
******************************************************
* 24x7 Scheduler Java API v4.1. *
******************************************************
* Unauthorized use or distribution of this program *
* may result in severe civil and criminal penalties, *
* and will be prosecuted to the maximum extent *
* possible under the law. *
******************************************************

Any help/guidance to allow me to get this working would be much appreciated...

Kind Regards,
Adam.

Mon Aug 08, 2011 5:06 am

SysOpSite Admin

Joined: 26 Nov 2006Posts: 6776

Hi,

To correct the issue you will need to alter a couple of tables in DB_AUDIT schema in the repository database.
Firs t in the repository database run
SELECT object_name(id) AS table_name FROM syscolumns WHERE name = 'xactid'

In found tables alter 'xactid' column data type to nvarchar(14). Just in case... the tables are in DB_AUDIT schema.

Please let us know if that doesn't help

Mon Aug 08, 2011 7:46 am

nski79

Joined: 04 Aug 2011Posts: 4Country: United Kingdom

Thanks for the quick reply!

I've made the change as mentioned, and now the staging table is being populated with 6830 rows, however, the job is still failing as below:

Code:

Connecting to Alert Center...

******************************************************
* Copyright (C) 2000-2007 SoftTree Technologies, Inc.*
******************************************************
* 24x7 Scheduler Java API v4.1. *
******************************************************
* Unauthorized use or distribution of this program *
* may result in severe civil and criminal penalties, *
* and will be prosecuted to the maximum extent *
* possible under the law. *
******************************************************

Using the Central Repository Deployment Tool please change audit data replication options for Sybase database servers to not delete data from sysaud_NN tables, keep all days. If properly configured Sybase takes care of audit data deletion automatically. This will make the job not to execute DELETE command and resolve the issue

Tue Aug 09, 2011 10:38 am

nski79

Joined: 04 Aug 2011Posts: 4Country: United Kingdom

Hi,

Thanks for the info, this has resolved the 'You can only use SELECT or SELECT INTO with the special table.' issue we were getting and the replication is now working as expected.

Thanks again.

Regards,
Adam.

Wed Aug 10, 2011 7:37 am

SysOpSite Admin

Joined: 26 Nov 2006Posts: 6776

Hi. thank you for the status update. Glad to know it is now working for you. If anything else comes up, please let us know.