Dump Df Not Working On Couple Of Vst's

catch.saravana

catch.saravana

Member
I understand this question is of less importance but just noticed one thing when I was generating DF file for all tables including VST's; _sysdbauth & _systabauth doesn't get generated as part of DF even if it is selected. I used Data Dictionary > Tools > Data Administration > Admin > Dump Data & Definition > Data Definitions.

I usually have this DF generated to quickly search description of each tables (like a Master DF) and was surprised when I didn't see these 2 tables on it. Any specific reasons?
 
The _sysdbauth and _systabauth tables are system tables but they aren't VSTs. They are SQL tables that contain info about DBA permissions and SQL users' permissions. They are not in the PUB schema, so ABL clients like the Data Dictionary can't see them.

There are separate utilities for dumping and loading that data. They are called sqldump and sqlload. They are documented in the SQL Development manual, OpenEdge SQL DDL chapter, Working with SQL utilities.
 
Rob Fitzpatrick said:
There are separate utilities for dumping and loading that data. They are called sqldump and sqlload.
Click to expand...
@Rob Fitzpatrick Good info! I wasn't aware that normal binary DL will not work on these tables as eventually as part of migration we will need to load the data from that table like how we do for _user table. Thanks Rob.

Rob Fitzpatrick said:
They are not in the PUB schema, so ABL clients like the Data Dictionary can't see them.
Click to expand...
To be honest I didn't know that it's not in PUB schema; do we have any progress provided documentation for this? The thing that I was surprised was I can see these tables on Data Dictionary whereas it's not part of DF that is generated (as i said i use that for reading the desc of the table).
 
One more question, Are all other vst's part of PUB schema other than _sysdbauth and _systabauth? I raised this question because I quickly double checked my db analysis report and only the user created tables are PUB.table1, PUB.table2 etc. None of the VST's are with 'PUB.'

RECORD BLOCK SUMMARY FOR AREA "Schema Area" : 6
-------------------------------------------------------
-Record Size (B)- ---Fragments--- Scatter
Table Records Size Min Max Mean Count Factor Factor
PUB.table1 32 2.4K 47 97 75 32 1.0 1.3
PUB.table2 78817 17.1M 127 763 227 78817 1.0 1.3
PUB.table3 1 46.0B 46 46 46 1 1.0 1.0
_Db 1 628.0B 628 628 628 1 1.0 1.0
_Field 15988 2.9M 153 359 190 17847 1.1 4.7
_Field-Trig 0 0.0B 0 0 0 0 0.0 0.0
_File 1063 275.0K 156 1811 264 1526 1.4 5.1
_File-Trig 1156 47.2K 37 59 41 1156 1.0 3.5

I understand that it's not a good practice to have user tables under schema area but as part of migration I am moving it to a different area - but this is data is from my current system to show PUB is there on user tables and not on system tables.
 
I need to retract part of what I said; I hope this will clarify:

Code: 
for each _file where _file-name matches "*auth":
  display
    _file._owner
    _file._file-number
    _file._file-name
    _file._tbl-type
    _file._tbl-status
    _file._category
  .
end.

Code: 
Owner                            File-Number File-Name                        Tbl-Type _Tbl-Status category     
──────────────────────────────── ─────────── ──────────────────────────────── ──────── ─────────── ──────────────
SYSPROGRESS                            32774 SYSCOLAUTH                       V                    SQL_AUTH_VIEW
SYSPROGRESS                            32772 SYSDBAUTH                        V                    SQL_AUTH_VIEW
SYSPROGRESS                            32812 SYSSEQAUTH                       V                    SQL_AUTH_VIEW
SYSPROGRESS                            32773 SYSTABAUTH                       V                    SQL_AUTH_VIEW
PUB                                      -83 _Syscolauth                      S        Y           SQL_AUTH     
PUB                                      -81 _Sysdbauth                       S        Y           SQL_AUTH     
PUB                                     -121 _Sysseqauth                      S        Y           SQL_AUTH     
PUB                                      -82 _Systabauth                      S        Y           SQL_AUTH

The tables whose names begin with "_" are in the PUB schema and thus visible to ABL clients. But if you query them you see no data. The tables you can query from SQL, in the sysprogress schema, contain the data you're looking for. Presumably that's why sqldump and sqlload are separate utilities, not part of the data dictionary.
 
catch.saravana said:
One more question, Are all other vst's part of PUB schema other than _sysdbauth and _systabauth? I raised this question because I quickly double checked my db analysis report and only the user created tables are PUB.table1, PUB.table2 etc. None of the VST's are with 'PUB.'

Code: 
RECORD BLOCK SUMMARY FOR AREA "Schema Area" : 6
-------------------------------------------------------
                                       -Record Size (B)- ---Fragments--- Scatter
Table                Records    Size   Min   Max  Mean      Count Factor  Factor
PUB.table1                32    2.4K    47    97    75         32    1.0     1.3
PUB.table2             78817   17.1M   127   763   227      78817    1.0     1.3
PUB.table3                 1   46.0B    46    46    46          1    1.0     1.0
_Db                        1  628.0B   628   628   628          1    1.0     1.0
_Field                 15988    2.9M   153   359   190      17847    1.1     4.7
_Field-Trig                0    0.0B     0     0     0          0    0.0     0.0
_File                   1063  275.0K   156  1811   264       1526    1.4     5.1
_File-Trig              1156   47.2K    37    59    41       1156    1.0     3.5

I understand that it's not a good practice to have user tables under schema area but as part of migration I am moving it to a different area - but this is data is from my current system to show PUB is there on user tables and not on system tables.
Click to expand...

All system tables have negative file numbers (_file._file-number), except for the SQL views, as you've seen above. VSTs are system tables but not all system tables are virtual (VSTs). VSTs all have file numbers < -16384. All other system tables have higher numbers and are real (i.e. exist on disk), not virtual.

The fact that the tables shown in your dbanalys output aren't prefixed with "PUB." is just how the code is written; it doesn't reflect the schema. All application tables you add via ABL and all system tables with _file-number < 0 are in the PUB schema. Here is similar output from an 11.4 table analysis report, reflecting this fact:

Code: 
RECORD BLOCK SUMMARY FOR AREA "Schema Area": 6
-------------------------------------------------------


RECORD BLOCK SUMMARY FOR SHARED TABLES
--------------------------------------------


                                                           -Record Size (B)-           ---Fragments--- Scatter
Table                                    Records    Size   Min   Max  Mean                Count Factor  Factor
PUB._Client-Session                            0    0.0B     0     0     0                    0    0.0     0.0
PUB._Codepage                                  0    0.0B     0     0     0                    0    0.0     0.0
PUB._Collation                                 0    0.0B     0     0     0                    0    0.0     0.0
PUB._Constraint                                0    0.0B     0     0     0                    0    0.0     0.0
PUB._Constraint-Keys                           0    0.0B     0     0     0                    0    0.0     0.0
PUB._Db                                        1  652.0B   652   652   652                    1    1.0     1.0

Also, dbanalys/tabanalys only shows info about real tables, not virtual tables.
 
You must log in or register to reply here.
Back
Top