SQL Scripts

This transaction allows you to carry out the following tasks:

• Performing SQL commands
• Viewing database information
• Performance analysis, if Oracle or MariaDB is used as database system

The following database systems are fully supported by SYSSQL:

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

Execute SQL
Database Information
Analyze - Oracle
Analyze - MariaDB
Analyze - PostgreSQL

This command executes the script displayed in the field 'SQL Script'. The script can be entered manually or you can load it via the button [SQL Script] into the field 'SQL Script'. Multiple SQL commands can be executed at the same time. They have to be separated with ';'.

With this command you can undo all changes or updates in the database as far as the last successful 'Commit'.Note: A 'commit' entered and executed in the field 'SQL Script' is the same as clicking the button [Commit].

All changes or updates in the database will be irrevocable after clicking this button.Note: A 'rollback' entered and executed in the field 'SQL Script' is the same as clicking the button [Rollback].

You can delete the content of the field 'SQL Script' via this button.

The popup dialog 'Load or Save SQL script' is displayed after clicking this button. You can perform two steps via this dialog box:

• Loading an existing SQL script from the file system
  
• Saving the content of the field 'SQL Script' to a file system

All SQL commands which have been executed on the panel 'Execute SQL' (also the commands triggered via button) are written into the log. The SQL commands used in the panel 'Analyze' and the ones which are necessary to determine the Execute Explain Plan will not be written into the log.

By clicking the button [SQL Date] the date defined in the field above is added to the selection text (specified in field 'SQL Script'). The date format depends on the database used.

This button is only active, if an Oracle database is used. The Explain Plan displays the order sequence optimized by Oracle Optimizer for SELECT, UPDATE, INSERT and DELETE in a consecutive form.

When a result set of a select is displayed, the columns can be displayed as plain text, in a streamgrid or in HTML view type. in viewtype streamgrid or HTML column names can be displayed in the header, if the column names can be determined from the database. If the view type 'Plain View' is selected, the records of the result set will be displayed in a normal stream. So the records can be taken with Copy and Paste.
If the column names cannot be determined, the result set will be displayed in plain view.

This panel displays detailed information of the database. If the database supports schemata, a schema can be selected in the field 'Schema'. Otherwise, this field is disabled. After selecting a schema, all tables of the schema are loaded into the selection list 'Database Tables'. Tables can be selected from this list. The total amount of all column lengths of this table is displayed in the field 'Sum of Column Lengths'. The field '# Rows in Table' displays the number of records currently stored in this table.

The field 'Table Structure' displays all columns with the respective definitions (Data Type, Length, Precision, Scale (number of decimal places), and Nullable{if a column may contain a null value}).

The 'Indexes' field shows the indexes of the table. By clicking on a table row, the columns of which the index is created from is displayed in the table 'Columns'.

By clicking the button [All to Log] the table structures of all available database tables are written into the log. The button [To Log] is used to write only the table structure of the table currently selected in the combobox 'Database Tables' into the log.

This panel is only available for Oracle database systems. It is used to display and to analyze SQL records in the database. The SQL statements to be searched for are specified in the field 'Include Patterns'. The SQL statements not to be displayed in the list are entered in the field 'Exclude Patterns'. The search is started via the button [Read] and the search result will be displayed in the table below. The sorting order can be selected via combobox 'Read Order'. The result list can be limited via field 'Limit Result Set to'. The button [Raw] is used to display the data in a block field, so that they can be, for example, copied and pasted into an Email.

In the list the following information is displayed for each SQL statement:

• The SQL statement itself
• The number of executions
• The number of lines that have been modified or loaded
• The number of disk accesses (total and per execution)
• The number of buffer accesses (total and per execution)
• CPU time (total and per execution)  - from Oracle Version 9
• Name of the module/program that has executed the SQL statement - from Oracle Version 9

The 'Oracle Explain Plan' can be displayed either by double-clicking the corresponding line or by selecting the line and subsequent click on button [Expl. Plan]. In doing so the intra-Oracle execution of the SQL statements is displayed.

This panel is only available for MariaDB database systems. It is used to analyze SQL Statements. The SQL statement has to be entered in the upper stream. After inserting the SQL statement the utton 'Analyze' has to be pressed. After that the result is displayed in the lower stream.