DBUNLOAD option to use data file names based on table names

[VolkerBarth]

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

[VolkerBarth]

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

[former_SQLA_member1694886 · Accepted solution]

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:

begin
  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
    do
    set @string=@string+'\\x0D\
'+'LOAD TABLE "DBA"."'
      +table_name+'"\\x0D\
'+'FROM ''D:\\\\\\\\DATABASES\\\\\\\\'
      +table_name+'.dat''\\x0D\
'+'FORMAT ''ASCII'' QUOTES ON\\x0D\
'+'ORDER OFF ESCAPES ON\\x0D\
'+'CHECK CONSTRAINTS OFF COMPUTES OFF\\x0D\
'+'STRIP OFF DELIMITED BY '',''\\x0D\
'+'ENCODING ''windows-1252''\\x0D\
'+'go\\x0D\
';
    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
    do
    set @string=@string+'\\x0D\
'+'RENAME ' +string(table_id)+'.dat '+table_name+'.dat\\x0D\
';
    call xp_write_file('D:\\\\DATABASES\\\\rename.bat',@string) 
 end for
end

[VolkerBarth · Accepted solution]

This is a great workaround, thanks!

[VolkerBarth · Accepted solution]

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