The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

AFAIK, DBUNLOAD names the data files it generates based on their table id, leading to filenames like 393.dat, 394.dat and the like.

This is usually handy and omits problems with table names that would be problematic as file names, e.g. in exotic charsets or when several owners use the same table name.

However, the current naming scheme makes some comparisons very difficult, both when

  • comparing the unload directories of two similar databases or
  • the reload.sql script of a database before and after a version upgrade.

In such cases the table ids of the same tables (i.e. the same owner/table name) may be different, sometimes because one database may have had some more CREATE/DROP sequences or had interim tables with effects on the sequence of table ids generated. - When migrating databases to newer versions, table ids usually change simply because newer versions have typically more system tables.

In such cases, one would have to compare, say, file 392.dat of database 1 with file 412.dat of database 2 and the like, making automatic comparisons very difficult.

Therefore I would suggest to add a DBUNLOAD option to use a file naming scheme based on table names (and owners). I'm sure problematic characters could be masked/replaced in an appropriate way. The goal for such cases should not be to use the exact table name but to choose a unique representation.

It should then generate files like Customers.dat (or DBA_Customers.dat), Products.dat and the like

asked 21 Sep '10, 10:23

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

Yes, I'm just trying to compare databases while migrating from ASA 8 to SA 12...

(21 Sep '10, 10:25) Volker Barth

I agree with the feature request. More functionality to manage schema changes and data comparisons would indeed be welcome.

Failing that I currently use a proc that generates a .bat file to do the renaming, like so:

  declare @string long varchar;
  set @string='';
  for names as curs_0 dynamic scroll cursor for
    select table_id,table_name from systab
      where table_type = 1
      and creator = 1
    set @string=@string+'\x0D\x0A'+'LOAD TABLE "DBA"."'
      +table_name+'"\x0D\x0A'+'FROM ''D:\\\\DATABASES\\\\'
      +table_name+'.dat''\x0D\x0A'+'FORMAT ''ASCII'' QUOTES ON\x0D\x0A'+'ORDER OFF ESCAPES ON\x0D\x0A'+'CHECK CONSTRAINTS OFF COMPUTES OFF\x0D\x0A'+'STRIP OFF DELIMITED BY '',''\x0D\x0A'+'ENCODING ''windows-1252''\x0D\x0A'+'go\x0D\x0A';
    call xp_write_file('D:\\DATABASES\\reload.SQL',@string) end for;
  set @string='';
  for files as curs_1 dynamic scroll cursor for
    select table_id,table_name from systab
      where table_type = 1
      and creator = 1
    set @string=@string+'\x0D\x0A'+'RENAME ' +string(table_id)+'.dat '+table_name+'.dat\x0D\x0A';
    call xp_write_file('D:\\DATABASES\\rename.bat',@string) 
 end for
permanent link

answered 21 Sep '10, 15:54

Vincent%20Buck's gravatar image

Vincent Buck
accept rate: 15%

Comment Text Removed

This is a great workaround, thanks!

(21 Sep '10, 16:12) Volker Barth

Just to add: During a V11/V12 migration test, I just could use your proc today - again a BIG thanks!

(17 Dec '10, 10:36) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 21 Sep '10, 10:23

question was seen: 2,043 times

last updated: 21 Sep '10, 15:54