This transaction is used to manage all tables included in the database.

When the transaction is started, the user gets a pop-up if any table is missing the group association (<sta> or <dyn>). This means that for every defined table, a rule “SdbGetTblGroup” must be defined to associate it with the group.

If several installations have access to the same database, it is possible that some installations will be using the database table COU as a counter while other installations will be using sequences. Thus, when this transaction is started a check is made to see whether both the database table COU and the sequence SSN are available at the same time in the database currently used. If this is the case, the user will be prompted to delete either the table COU or the sequence SSN, depending on current session settings.

The following database systems are supported by SYSDBA:

• Oracle
• IBM DB/2
• MS SQL Server
• MySQL
• MariaDB
• PostgreSQL
• MS Access

If database descriptions have changed due to information in fields (table columns) being inserted, changed or deleted, the database can be updated by creating and executing the migration script.

Not all commands are applicable for all database types. For example, in an IBM DB2 database system columns cannot be dropped. In this case, the relevant tables have to be updated using the following steps.

1. Export the selected table(s)
2. Delete the selected table(s)
3. Create the selected table(s)
4. Import the selected table(s)
5. Repair the INR counter

By running through these steps, database descriptions can be updated and system dumps can be avoided because of errors occurring in database descriptions.

By using an alphanumeric INR, the range of values of an INR can be extended from 100,000,000 to 2,037,468,266,496 (26*36^7). Counters with numerical numbers greater than 100,000,000 can be entered using either numeric or alphanumeric characters in this transaction.

Example for the conversion of an alphanumeric INR into a numeric equivalent:

A0,000,000 = 100,000,000

Using single byte and multi byte character columns in database or tables at the same time will result in slower performance.

The panel Import/Export Profiles allows to copy user profiles between flat files (*.tdp) and database in both directions.
It also indicates the source that is currently used for the storage of the profiles.

To copy profiles:

• Select the necessary profile(s) in the menu
• Choose direction (from flat files to database / from database to flat files)
• Click button Copy

This can be done also via command line using the following switches:

If imported/exported in the background using the switch - all profiles are selected by default.

Database
Oracle specific
Show Processes
Processes per Session
DB2 Mainframe
Import/Export Profiles

Tables are selected as follows:

Once the respective table has been selected from the list of available tables, further processing can be started. The selection remains as is until changed, enabling subsequent steps for a table to be carried out directly.

The selection is based on the table groups available:

• <all> - contains all tables
• <dyn> - contains all tables with dynamic data (e.g. contracts, related transactions, etc.)
• <sta> - contains all tables with static data
• <sys> - contains all tables with system data
• <for> - contains all “foreign” (non-application) tables. None of these tables is contained in another list. The “Include foreign tables” checkbox allows the user to add or delete foreign tables from the list of tables for the operation (e. g. Export Table)
• <lst> - allows tables to be selected from a list initially assigned to another table group. At first, <all>,<dyn>,<sta>, or <sys> needs to be selected. This activates the “Selection of Tables” field. The selection can now be edited.

The field on the right hand side of the panel displays tables assigned to the respective table group.

When a new table is created in the application, rule “SdbGetTblGroup” must be defined to get the information about the membership of the different database table to the groups for static, dynamic and system table.

The groups <sta> , <dyn> and the information about connected directories are no longer taken from SYSDBA.INI but from the rules in the sources.

The following functions are available:

Exports the selected table(s) which have been selected via the field “Table or Group to be processed” to the defined “File Path for Import/Export”. A new file <Table>.DBE is created for every table to be exported.

After checking the checkbox “Overwrite existing export files without prompting” the user can overwrite already existing export files without the user confirmating every single file. This checkbox is unchecked by default.

Imports table(s) that have been selected using the “Table or Group to be processed” and “File Path for Import/Export” field into the database. During the import, a file with the name <Table>.DBE is created for each table. These files are then imported afterwards.

If the checkbox “Ignore errors of undefined columns on import” is checked, columns missing in the import file and/or in the table will not result in an error. This checkbox is checked by default.

Deletes the content of the selected table(s) without deleting the table descriptions.

Deletes the selected table(s) completely, i.e. including content and table descriptions from the database.

Creates the selected table(s). If the checkbox 'Use creation Script' is checked, tables will be created with creation script, otherwise with the internal function DBCreate. If the directories required for the table(s) have to be created in the partition 'data', the respective directory descriptions are read from the INI file SYSDBA.INI in the section [DATA-Directories]. If they do not exist, they will be created.

Checks the selected table(s) of the database with respect to correct column and index definitions when compared to the database description of the modules.

Repairs the INR counter for the selected table(s).

Allows the database content of all tables to be displayed using the appropriate xxxGET module.

Allows counter tables to be edited. From version 2.2 and above counter stepping is fix set to 1, the [Count-Stepping] section in 'sysdba.ini' is obsolete.

This function checks the selected table(s) of all fields to which one or more codetable(s) has been assigned to ensure if the field content is also contained in the codetable. After processing, a prompt dialog is displayed, notifying the user of about the number of missing entries. These information can be viewed in the log.

If “Insert missing” is checked, any missing entries are automatically inserted into all codetables. If there are multiple codetables for one field, this indicates that there are various decodings (longtexts) available for this codetable. For example, a code can be converted into SWIFT code or into longtext. In both codetables, a decoding method must be specified for each type of data content available.

If the option '<all>' under “Table or Group to be processed” is selected for all database tables, the checkboxes “Unused to Log” and “Xref” also become enabled. If “Unused to Log” is checked, all unused code values are written to the log. By checking the checkbox “Xref”, all cross references of the codetables will also be displayed in the log.

Allows all system locks to be handled.

The script is displayed in a new panel and can be executed directly using [Execute]. It is also possible to save the script first by clicking [Save] and to execute it later, for example, with the transaction “SQL Scripts” (SYSSQL). The new panel can be closed by clicking [Close]. The script generated is also be displayed in the log.The script generated contains the DDL commands to adapt the available tables and indices in the database to the database descriptions of the modules. This includes:

• Generating columns,
• Deleting columns,
• Generating indices,
• Deleting indices
• Change indices
• external tables.

When using the migration script, the surplus columns and indices are deleted by default, but not the external tables. These can be edited by checking or unchecking the respective checkboxes.

Columns that are contained in an index cannot simply be changed. To do so requires the index to be dropped first, and then generated again once the column has been changed. However, this can only be done for indices that are known to the system, These are indices that are either defined in the module, or listed as additional indices in SYSDBA. If the column is defined in a different index, a warning is recorded in the log, and no migration script is generated, unless “Delete Index” is checked. Doing so drops all extraneous indices.
Indices cannot be dropped for MariaDB and MySQL database systems, when other sessions work on this database. In such a case a prompt will be shown and the migration script will not be executed.

Creates an SQL script for the selected tables(s) containing the 'Create' commands for generating the table and the indices.The script is displayed in a new panel and can be executed directly using [Execute]. It is also possible to save the script first by clicking [Save] and to execute it later. The new panel can be closed by clicking [Close].

The migration script and creation script can also be generated in batch mode.
Command line switches:

“-cresql <File Name> Creation Script”
“-migsql <File name> Migration Script”

If no file name is inserted:
“Creation Script: Name=getpath('TMP')+'dbcreate.sql'“
“Migration Script: Name=getpath('TMP')+'dbmigrate.sql'“

”-w” for overwriting an existing file

If an error occurs in the batch, a returncode is set (also for ”-i” and “-o”).
“-i <Path>” Creates the database for the application
“<Path>” = Optional path of the dbe files. If “-o” has no path => “getpath( data )\dbe”

Available actions:
- Creating all tables
- Importing all tables from the dbe files
- Repairing all INR counters

“-o <Path>” Exporting a database of the application into dbe files
<“Path”> = Optional path of the dbe files. If “-o” has no path => “getpath( data )\dbe”

“-d” for 'Dropping' indices and columns in the migration script

Available actions:
- Repairing all INR counters
- Exporting all tables into dbe files

Additional Indexes which are not defined in the DOKA modules, can be specified using the button [Addit. Indexes] A name and the corresponding generation SQL are specified here. If the name of an index matches an index already defined in the module, the additional index replaces it. Via checkboxes it is possible to show whether the statements for generating the index and dropping the index are to be generated in the scripts (generation script and migration script).

Creates a SQL script to convert all CHAR, VARCHAR and CLOB Columns in place into NCHAR, NVARCHAR and NCLOB columns. This functionality is available for Oracle and MS SQL Server database systems.
Algorithm:

• Rename existing table
• Create table with NCHAR, NVARCHAR and NCLOB columns
• Insert rows from old table into new table
• Drop old table

A sequence is an object that can be used to generate unique numbers. Sequences are located outside of a database transaction. The following database systems support sequences:

• Oracle
• IBM DB/2
• MS SQL Server

If the application is running with counters in table COU and not with sequences, the button [Convert>SEQ.] becomes visible. In this way the system can switch from counters in table COU to counter in sequences. Using counters in sequences leads to a higher performance than using counters in table COU.

The checkbox “Selected Export, Delete or DbSelect” allows the definition of criteria ('where' clause) for a selected table, if this table should be exported or deleted or if the field INR should be renumbered.

The log with the all processed actions after the start of the transaction is displayed by clicking the [View Log] button

If errors occur, a prompt dialog shows the number of errors at the end of the process. These errors can be viewed in the log.