Administration Component

Database-Specific Admin Notes

Table of Content

Table of Content

Table of Content

The following sections will be expanded as we understand the peculiarities and limitations of the various database systems.

DB2 z/OS

Foreign Keys

Information on these is difficult to retrieve from the catalog tables. At this stage we only display the columns in the referencing table. The columns in the referenced table cannot be displayed.

Primary Keys

You can only create a Primary Key if you already have a unique index on the columns (unlike with other databases, creating a Primary Key does not create an index).

Stored Procedures

AQT can build and manage Native SQL Stored Procedures for DB2 z/OS. These were introduced in DB2 for z/OS v9.

DB2/UDB

Tablespaces

For DB2/UDB Version 7 or earlier, information on Tablespace Containers cannot be obtained using SQL. This means that:

  • when the Properties of a Tablespace is displayed, the Container information is not shown.

  • DDL for tablespaces cannot be generated

These restrictions do not apply to DB2 v8 and higher, for which container information can be obtained using SQL.

Functions and Procedures

  • Functions and Procedures are awkward to deal with, as there can be multiple functions/procedures with the same name. What distinguishes these multiple objects is the specific name, which must be unique. AQT uses the specific name when displaying the information on the object.

    For this reason, when you create a new function or procedure, AQT will require you to give the specific name for the procedure / function. Without this it is difficult for AQT to manage the object.

  • Table and row functions have not been implemented.

Function authorities

Authorities cannot be granted or revoked from system functions (SYSFUN and SYSPROC). Consequently, when you using Manage Authorities for a user or group, AQT will exclude the system functions from the list of functions.

Compiling Stored Procedures

For DB2/UDB (prior to version 9), if you had an error trying to create a Stored Procedure you often got a message such as:

SQL7032N SQL Procedure 'xxxx' not created. Diagnostic file is "P1234567.log"

DB2 creates Stored Procedures by building and compiling a C program. This has failed to compile. The P1234567.log file has the compile errors which generally point to the cause of the problem.

The log file is held in a very obscure place - this is:

%SQLLIB%\FUNCTION\ROUTINES\sqlproc\database\username\tmp

where database is your database name, username is your user name and %SQLLIB% is your DB2 install directory (normally C:\Program Files\IBM\SQLLIB\).

SAP/Sybase ASE

Defaults

When you create a table, you will notice that columns have two parameters - Default Value and Default Object.

  • Use Default Value if you wish to specify a particular value for the default. Note that you should enclose any string values in single-quotes.

  • Use Default Object if you have already created a Default (using the Create Default command) and wish to link this column to that Default.

Dropping Indexes

By default, you can only drop indexes on tables for which you are the owner. If you wish to drop the index on tables owned by another user, you must first use the setuser command.

Example: to drop index custix1 on table test.customers:

setuser 'test'

drop index customers.custix1

Roles

  • You cannot create or alter a role unless you are in the Master database

  • When you alter a role, most of the fields will be blank. This is because the information on the role cannot be retrieved from the database. However you can still amend the fields to new values.

  • The encrypted password is displayed in the password field. This is not readable, however it does show you that there is a password on the role.

  • If you wish to change a role's password you must first remove the password, then set a new one. You remove a password by setting it to blank (then clicking on Run).

MS SQL Server

Defaults

When you create a table, you will notice that columns have two parameters - Default Value and Default Object.

  • Use Default Value if you wish to specify a particular value for the default. Note that you should enclose any string values in single-quotes.

  • Use Default Object if you have already created a Default (using the Create Default command) and wish to link this column to that Default.

Triggers and Procedures

If you select the Encrypted option, the trigger / procedure text will not be visible in the system tables. Consequently the trigger or procedure cannot be viewed or amended.

Renaming Objects

If you rename a view, trigger, procedure or function, you will have a mismatch between the object name and the name referred to in the Create text held in the database. This can result in the wrong name being used when the object is being amended, or when the DDL is generated.

Oracle

Stored Procedures

When you display a Stored Procedure in AQT, you can see:

  • the procedure text

  • the procedure parameters

There is one little trap with these - the system table that holds the procedure parameters is only populated when the procedure compiles successfully. So, if your procedure has not yet compiled OK, you can see the text for it in the Database Explorer, however the parameter-list will be empty.

When you create a new Stored Procedure, you enter the parameters on a separate tab in the window. However when you display or modify the procedure, you will not be shown this tab. This is because, if your SP has not compiled successfully, the parameters can not be shown. Instead you are shown the full procedure text.

Formatting in Views, Stored Procedures, Functions and Triggers

When you create or alter a View, Stored Procedure, Function or Trigger through AQT, a lot of the formatting is lost. This problem happens because the Oracle ODBC Driver removes duplicate spaces from your SQL.

The solution is to use the Microsoft ODBC for Oracle ODBC Driver.

Informix

Indexes

If you do not give an index a name, Informix will give it a system-generated name. This system-generated name is strange - a blank followed by a number. AQT currently has trouble with index names that start with a blank - some functions (such as Gen DDL) do not work for indexes such as this.

MySQL

Foreign Keys

  • You can only define foreign keys for tables using the InnoDB file system.

  • The creation of a foreign key fails unless you have a primary key defined on the referenced table, and an index on the foreign key columns (in the source table). If you do not have these, the creation of the foreign key fails with Can't create table (which is not a meaningful error message).

  • AQT cannot display the Foreign Key information. This information appears not to be available through the MySQL ODBC driver.

    The only way you can see the Foreign Key information is to view the table DDL (using the Generate DDL button or right-clicking a table and selecting Table DDL). This runs a MySQL function that retrieves the table definition.

SQLBase

Procedures

Although AQT displays procedures from all schemas, you can only manipulate procedures in your own schema (eg. where the schema name is your user name). The commands for creating and dropping a procedure do not allow you to specify the schema name; so they will be created / dropped from your own schema.

You should not alter / redefine a procedure that is in a schema different from your own. Procedures are implicitly created in your own schema. If you alter a procedure in a different schema, the changes will be applied to a procedure with the same name in your own schema instead, or if there is none, one will be created of that name.

Views

Check Option doesn't appear to be set correctly in the SQLBase System Tables, so will not display correctly.

Pervasive.SQL

Copy Table

You need to be careful when you create a table based on the definition of another table (using the Copy from) button. The table File (on the General tab) will be set to the value from the table you have copied. You must change this to another value (or blank it out). If you don't do this, the table will be created using the same file. This can cause all sorts of problems!!

If you have done this, drop the table you have just created using the Drop from Dictionary only option.

SQLite

There are currently the following limitations with the admin component for SQLite. Many of these limitations are due to the limited amount of information available in the SQLite system table (sqlite_master), or bugs/limitations of the SQLite ODBC Functions.

  • constraint names (for Primary Key and Foreign Keys) are not displayed correctly.

    • DDL for tables will correctly have the correct constraint names

    • Properties / Alter and Redefine for table will not have the correct constraint name. AQT will generate names for these as (for the Primary Key) pk_tablename and (for Foreign Keys) fkn where n is an incrementing number.

  • DDL for Descending Index columns are generated as Ascending columns.

  • AQT has limited support for table Check Constraints. You are able to create a table with Check Constraint, however these will not be displayed when you display the table properties. The table DDL will include the check constraints.