Skip to Content
avatar image
Former Member

Sybase Database Dumping Error

Background: InfinityQS ProFicient 4.4 is a Statistical Process Control software that collects our manufacturing data. There are 2 databases for InfinityQS ProFicient. The project is to migrate 2 Sybase databases (800 MB Database & 2 GB Database) to SQL.

The solution we have to migrate the 2 database is to use the InfinityQS Database Manager's Dump & Load feature, which is part of the InfinityQS ProFicient software package. The Dump & Load feature export out the tables as individual TXT files to a specific location on your local drive, then after you connect to the new database, you can load those TXT files into the the database. We tested out with the 800 MB Database and this method worked with no problem. Database was migrated successfully as we compared the statistics for the original 800 MB Sybase database and the new 800 MB SQL database.

Problem: As we move on and test dump the 20 GB Sybase database, and error occurred as shown on the picture. I have worked with engineers from InfinityQS, and our Database Managers to trouble shoot this problem, but it's still unsolved.

Observation:

- Error happens when Database Manager tries to dump a new table

- The TXT file empl-inf.txt is an example for an unsuccessful dump, note that "sybfi2_1" is a column name, but the TXT file contains no data (columns are empty). Table ENPL-INF contains employee data information in the Sybase database, so the TXT file is supposed to contain data also.

- Database Manager says that "sybfi2_1" is an Internal Sybase Index, which cannot be created or dropped by her.

What we have tried:

1. We were wondering if size was the problem, so we tested out with a backed up 8 GB database which was the same database as the 20 GB about 7-8 years ago, and it gave us the same problem.

2. We tried to turn on the extensive logging to look for clues: extended-logging-iiwdblib.txt

What we are looking into:

Not all tables have that error, so we are trying to load whatever was dumped into a database for comparison.

Nothing much, our ears are opening to any suggestions.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Feb 27 at 02:31 PM

    I don't have an ASE instance handy at the moment, but I seem to recall the 'sybfi2_1' column is auto-created by ASE when a function-based index is created on the table, eg:

    create index idx_mult on mytable ( col1 * col2 )

    Since an (ASE) index is built on actual data, what ASE will do is alter the table and add a materialized computed column, populate said column with data, and then build the index on this new column.

    Since the 'create index' command doesn't provide a method for naming the computed column, ASE will auto-generate a name for the column, eg, 'sybfi2_1'.

    If this describes what your system then the output from 'sp_help <table_name>' should show:

    • 'sybfi2_1' as a column in the table
    • 'sybfi2_1' as a column in one of the table's indexes

    ---------------

    While the table owner can explicitly add computed columns to a table ... and said columns can be queried ... I don't recall if the computed column created to support a function-based index can be queried (I'm leaning towards 'it cannot').

    If this is the case (ie, 'sybfi2_1' cannot be directly queried) then this probably explains the error you're receiving, ie, this migration tool is trying to select from, or in some other way reference, this column.

    And if this is the case, then the tool needs to be configured (re-coded?) to skip explicitly accessing the column; of course, at some point someone/something will need to ensure the function-based index (and it's underlying computed column) is created in SQLServer ... or whatever the equivalent feature is called in SQLServer.

    Add comment
    10|10000 characters needed characters exceeded