Administration Component

DDL, Authorities & Bulk Actions

Table of Content

Actions on multiple objects

This window is used for running actions on multiple objects. Open this window from the Database Explorer, by using the buttons in the Admin toolbar such as Drop, Rename, Table Comments, or other functions.

This window is part of the Administration Component of AQT.

Window usage

This is a generic window that can be used for performing a number of different functions. Examples:

drop tables
rename tables
set table comments
set column comments
analyse / runstats against tables

Below is the example of the window for Drop Tables.

Object List

When you start this window you will be shown a list of the objects (eg. tables) within your schema.

If you are displaying favorites in the Database Explorer, AQT will either show the favorite objects, or the objects in your schema, depending on the function being performed.

Selecting Items

To select an object to be processed (for instance, to drop a table), click the checkbox on the left.
In most cases, the object you had selected in the Database Explorer will be selected in this window.
If you are renaming a table or entering table / column comments, typing the text will automatically select the object.
To select all objects, Select > All (or Ctrl+A)
To select no objects, Select > None (or Ctrl+N)

Skeleton SQL

The SQL that AQT will use to perform the action on the objects is given in Script > View / edit skeleton SQL (or Ctrl+Q). This skeleton SQL is applied against each object you have selected in the grid.

As an example, the skeleton SQL used for dropping tables is drop table <$1:*>.<table:*>. The full syntax of skeleton SQL is not given here, however in the SQL above:

<$1> is your schema name
<table> is the name of the item in the Table column in the grid
:* is a function that is applied to these values. This function will enclose the name in quotes if that is required by your database type. This is useful if the name contains a blank.

If necessary, you can amend this skeleton SQL by changing it in the Skeleton SQL window. Any changes you make will apply only to the current window. The next time you activate this window, the skeleton SQL will revert back to the default value. If you want to make a permanent change to the skeleton SQL, use the Administration Configuration window.

Preview SQL

Displays the SQL that AQT will use to run to perform the action on the selected objects.

Run

Runs the SQL for performing the action on the selected objects. If this involves running more than one SQL statement, you will be shown the Run Multiple Statements window.

AQT keeps track of whether each SQL statement has been run successfully. If so, the row is de-checked. Any rows that fail to be processed will remain checked for you to correct and rerun. In Drop and Rename modes, the rows are not removed or renamed - if you want to see what effect your changes have had, click on Refresh to refresh the data in the display.

You can also run the SQL with F5.

Refresh

Clicking on Refresh will refresh the Object List. You might do this, for instance, after you have renamed or dropped some objects and wish to see the new list of objects.

Generate DDL

Use this window to generate DDL statements for your database objects.

DDL (Data Definition Language) refers to the SQL statements that create the objects (tables, views etc) in your database. Many people find it useful to generate and save the DDL for their database objects, as it gives them the ability to recreate their objects on that or another database.

DDL is part of the Administration Component of AQT.

There are two ways to start the DDL function. From the Database Explorer window:

click on a object, then click on the DDL button in the Administration toolbar. This will allow you to generate the DDL for all those objects in that Schema.
for some databases there will be a DDL button in the main menu. This will allow you to generate the DDL for all objects in the database, irrespective of the Schema.
If you have a lot of objects in your database, it can take a few seconds for AQT to retrieve the list of the objects to be displayed in the DDL window.

The Generate DDL function:

generates complete DDL for tables, including tablespace and other clauses
includes DDL for related objects such as primary key, indexes, foreign keys, constraints, views, aliases and triggers
can also include the Grant statements that establish the security rights for the table
can generate DDL for other objects than tables. Any object whose definition can be obtained from the system tables can have its DDL generated. This includes tablespaces, functions, procedures, bufferpools, users.

When you start the Generate DDL function, you will be shown all the tables (or other objects) within the current schema. You can select the tables you wish to generate the DDL for.

Options

There are a number of options for the way the DDL is generated.

Send Output to

This specifies where the generated DDL is to be written to.

Window. The DDL is displayed in a message-box. This is the only option available if you are in evaluation mode of the Administration Component.
Single file. The DDL is written to a file. You can specify whether the DDL is to Replace or Appended to the file.
One file per object. This option allows you to generate your DDL into separate files per table/object. This is useful when you are generating the DDL for multiple objects, and you want the DDL for each object in a separate file.
Directory. When you have specified One file per object, this gives the directory where the files are created.
File Mask. When you have specified One file per object, this determines the names of the files that will be created for the object DDL. AQT will substitute the object name (excluding the schema name) where <obj> is specified in this mask. For instance, if AQT is generating the DDL for table dbo.customers, <obj>.ddl will create this in a file named customers.dll.

Delimiter

This specifies the character used to delimit the multiple SQL statements in the DDL script.

Include DDL for

This list will contain the different parts of the DDL that can be generated. These will be:

your main object (eg. table)
the related objects (eg.primary key, indexes, foreign keys)
authorities for the object (if appropriate)

You can select which of these parts of the DDL are to be generated. At least one option can be checked. It is OK to generate DDL for a related object only (eg. for only the indexes and not the tables).

Warning: you should only include the options you need - every option you select will increase the number of queries AQT needs to run against your system tables, and will increase the time to generate the DDL.

Generate foreign key DDL at end of script

By default, the DDL for a foreign key will appear in the DDL script after the DDL for the source table. This can cause a problem when the script is run, as the foreign key cannot be created until after both the source and referenced tables have been created.

Avoid that problem by selecting this option. When this is done, the DDL for the foreign keys is placed at the end of the DDL script.

If you are generating DDL to separate files, the file name for the foreign key DDL will be taken from the File Mask, with fk replacing <obj>.

Generate View DDL at end of script

This is similar to the previous option. If you have multi-table views, these cannot be created until after the creation of all the tables that the views are based on. For this reason you may wish to have the view DDL appear at the end of the DDL script.

If you are generating DDL to separate files, the file name for the view will be taken from the File Mask, with view replacing <obj>.

If you have multi-table views, the views may appear more than once in the DDL script.

Generate Trigger DDL at end of script

This is similar to the previous options. If your triggers refer to several tables, these cannot be created until after the creation of all the tables. For this reason you may wish to have the trigger DDL appear at the end of the DDL script.

If you are generating DDL to separate files, the file name for the view will be taken from the File Mask, with trigger replacing <obj>.

Include commented-out Drop statement

When this option is checked, AQT will include a commented-out drop statement before the definition of each object in your DDL script. It is often useful to have such a drop statement in your scripts, as it makes it easy to drop and redefine your objects.

Include full DDL for related objects

The Include DDL for list contains your main object (eg. table) and all the related objects (indexes, views, triggers etc). When generating the DDL for the related objects the question arises as to how much if their DDL is to be generated. With Include full DDL for related objects is selected, AQT will generate the full DDL for the related objects.

For example: if you are generating the DDL for tablespaces, and you have selected that the DDL for the tables is also to be generated, this option will generate the complete DDL for the tables.

Generate For

This specifies the database for which the DDL is to be generated. By default this is the database from which you are generating the DDL. You can change this to another database type - AQT will then generate the DDL in the format required for that database.

This is a complex thing for AQT to do - it will need to convert table definitions from the format of one database to the format of another. It will also need to convert data types between different database types. There are limits to how well AQT can do this.

Type Mapping

This is used when the DDL is being generated for another type of database (Generate For is specified). This specifies how data types are mapped from the database to the Generate For database.

Example: specifying datetime->timestamp will ensure that datetime columns (in the database) are generated as timestamp columns in the DDL.

You can specify multiple mappings, separated by commas. Example: datetime->timestamp,bigint->double

Ensure lines are less than 72 bytes

When this option is selected, AQT will ensure that the generated DDL has a maximum line length of 71 bytes. This is useful if you are generating DDL which is to be transferred to a DB2 z/OS system.

This function will split lines at either a blank or a comma, unless these are within a string. If your DDL has large text fields, AQT will split the line in the middle of the text, which may not give correct results.

Format View definitions

When this option is specified, AQT will reformat the view definition into a more readable format. This is the same as using the Format SQL function of the Run SQL window.

Include a Commit after each object

When this option is specified, AQT will include a Commit statement at the end of the DDL for every object.

Generate

Use this button to generate the DDL for the objects you have selected. You can cancel the processing by clicking on Abort.

Saving and Loading your DDL Options

Once you have set up your options, you can save these with File > Save Options. The options will be saved to file on disk. These options include the Include DDL for... items, so will be specific for a type of object.

Once you have save the options, you can load them with File > Open Options.

Running DDL in Batch

You can run the DDL as scripting statement. To generate a script statement, use File > View Script Statement. You can copy this statement and use it in the Run SQL window or incorporate this in a batch file.

The script statement will generate the DDL for all objects in your Schema. You can specify a Filter to limit this to a given set of tables.

Manage Authorities

This window is used for managing the authorities of your database objects. This can be used to manage authorities of many types of objects - tables, views, packages, procedures, tablespaces, plus others.

This window is part of the Administration Component of AQT.

In this section we give a basic description of how to use the window. You should also read:

Additional Functions for a discussion on other features of this window.
Database Specific Notes for notes for DB2, SQL Server, Sybase, Informix and Pervasive.SQL.

Window Mode

As with other parts of the AQT Administration Component, the Manage Authorities window is used for many different functions and takes a different appearance depending on the function being performed and the database type.

It had three main uses:

Function	Description	Invoked from the DB Explorer by
Object authorities	Specifies which users have access to an object (such as a table)	Selecting an object then clicking on the Manage Authorities icon
User authorities	Specifies what objects a user has access to	Selecting a user then clicking on the Manage Authorities icon. This may be a drop-down list if there is more than one type of authority a user can have.
User roles	Specifies which users are members of which groups or roles	Selecting a user then selecting the appropriate option from the Manage Authorities drop-down list

Example

The following is an example of the Manage Authorities window for managing DB2 Table authorities

When the window first opens, it will show you the existing authorities for the object. If the object has no existing authorities, the grid will be blank.

You can add new objects (in this case Users or Groups) to the grid by clicking on one of the buttons in Add to grid.

Authorities

These are shows as follows:

Y	Has authority
G	Has GRANTABLE authority (this allows the user to Grant the authority to other users)
N or blank	Does not have the authority

You can refresh the grid by clicking on Refresh.

Changing the Authorities

You can change the authorities shown in the grid by any of the following methods:

Clicking on a cell and either selecting or typing Y, G or N.
Selecting a range of cells and clicking on Grant Access, Grantable Access or Revoke Access
Selecting a range of cells, right-clicking and selecting Y, G or N.

As you change the authorities, the cell will change to yellow (showing that there is a pending change). Once you run these changes and they are applied successfully to the database, the cells will change from yellow to white.

Clicking on Reset will remove any pending changes.

Run

Runs the SQL for granting / revoking the authorities you have selected. If this involves running more than one SQL statement, you will be shown the Run Multiple Statements window.

You can also run the SQL with F5.

AQT keeps track of whether each change has been run successfully. If so, the cell is taken out of pending-change status (and is changed from yellow back to white). Any changes that failed to be processed remain highlighted for you to correct and rerun.

If you are in evaluation mode of the Admin Component, this button will not be active.

Preview

Shows you the SQL that AQT will run to grant / revoke the authorities you have selected.

Reset

Removes any pending changes you have made to the grid.

Clear

This clears the existing contents of the grid. This is useful for removing the existing authorities so you can deal with the new ones you are adding.

Refresh

This refreshes the data in the display. You may wish to do this after you have made a number of changes to the authorities and wish to re-display the current authorities.

Sometimes, the changes you have made will not appear when you Refresh. This can be quite puzzling. This happens because some databases have complicated rules about whether an authority is recorded explicitly against a user. See Database Specifics for more on this.

If you have added any objects / users to the grid with the Show Tables or Show Users buttons then these will disappear from the grid. You will need to click on these buttons again to add these entries to the grid.

Additional functions

This section describes some additional functions you can do with the Manage Authorities window.

Grant Authorities on/for

When you start this window, the object or user you are dealing with will be shown in the Grant Authorities on/for textbox. This is the object or user the authorities will be granted to when you click on the Run button.

You can change this to another object / user, or specify a list of objects / users. This can be useful for:

granting authorities to a list of users/objects, rather than to a single one
copying authorities from one object/user to another (see the section Copying Authorities later)

Double-clicking will give you a larger box for typing the list of authorities.

Generate

These options specify what SQL you want to be generated.

Changes. In most cases, you will use the Manage Authorities window for changing the authorities for an object. In this case, Manage Authorities will generate SQL as per the changes you have made to the grid.
Grant all. This option is useful if you want a complete list of the current authorities of an object. When Grant all is specified, Manage Authorities will generate the SQL to grant all the authorities shown in the grid.
Revoke all. This is similar to Grant all, except that it revokes all the authorities. This is useful way of the revoking all the existing authorities of an object / user.

Revoking all Authorities

You can revoke all authorities from an object/user by:

Display the authorities for the object/user
Change Generate to Revoke All.
Click on Run.

Copying Authorities

Often you need to set up the authorities on an object/user to be the same as another object/user. You would do this when you have created a new object/user and wish to have this the same as another. Do this as follows:

Display the authorities for the object/user you are copying from
Type into Grant Authorities on/to the name of the object/user you are copying to. This can be a list of object/user names.
Change Generate to Grant All.
Click on Run.

Grant / Revoke SQL

The SQL used for Granting and Revoking authorities can be seen by Script > View / edit skeleton SQL for Grants / Revokes. You can change this skeleton SQL if necessary. Any changes you make will only apply to the current window. The next time you activate this window, the skeleton SQL will revert back to the default values. If you want to make a permanent change to the skeleton SQL, use the Administration Configuration window.

SQLID / Grantor

If your database is DB2 z/OS you will get a textbox labelled SQLID
If your database is Informix, this textbox will be labelled Grantor.

This allows you to run your Grant / Revoke statements under a different id. DB2 z/OS will set the SQLID. Informix will use the AS clause for the Grants.

Authorities: database-type specific notes

DB2 notes

For DB2/UDB, when you are displaying users you will have another column in the grid - Type. This is U (for user) or G (for group). DB2/UDB needs to know whether the identifier is for a user or a group.
For DB2, when you are granting object access to a list of users, you can add another user to the grid (the grid will have an insert-row at the bottom). This allows you to grant authorities to a new user. For DB2/UDB you will have to specify the Type (user/group) of this new user.

SQL Server notes

The owner of an object and the dbo user have implicit authority on an object. If you grant authority to these users, those authorities will not show in the Authorities grid when it is refreshed. This can be very confusing.
Application roles cannot have members assigned to them (unlike other roles). If you try to do this, you will get the message The role does not exist in the current database.

Sybase Enterprise notes

Sybase will not record that a user has access to a table, if this access has already been granted to the user's group.

For instance, if:

the group has select authority on a table
you grant select and update authority on the table to the user
a refresh of the Authorities grid will show that the user only has update authority on the table

This can be confusing for the novice DBA!

Informix notes

You cannot grant authorities on objects owned by someone other than yourself, unless you use the Grantor option. The Grantor option can only be done by a user with DBA authority.

If you try and grant authority on another user's tables, you will get the message No GRANT option or illegal option on multi-table view (which, in our opinion, doesn't describe what the problem is).

Pervasive.SQL notes

you cannot grant authorities to Views
when granting access to a table, you may get the message The owner name is invalid. This means that the table is password protected. The password is (misleadingly) called an owner in several places in Pervasive. You will need to specify the password with the Grant command. AQT doesn't provide an option to do this.
when granting access to a table, you may get the message Cannot grant synonym privileges.This happens when the user is a member of a group; you should grant access to the group rather than the user.

User-defined Keys

There are a number of places in AQT where you need to specify a unique key for your table/view. For instance:

when editing a table using inline edit
when updating / delete rows using row update / delete
using the Data Compare
when using Update Mode with the Data Loader

In all these cases, AQT will try to automatically determine the unique keys for the table. It does this by:

if the table has a Primary Key, AQT will use these key columns
if your table has a Unique Index, AQT will use the keys of this index. If your table has multiple unique indexes, the first index that AQT finds will be used.

If your table has neither a primary key nor a unique index, AQT will prompt you for the columns that comprise the unique key of the table. After you have selected these, AQT will then ask you whether you want AQT to remember this key. If so, AQT will create a user-defined key for the table/view.

AQT will use that key when you are using that table in the future; this saves having to specify the key columns every time.

User-defined keys are particularly useful if you are dealing views, since primary keys and unique indexes cannot be defined on views.

Other notes:

User-defined are keys are used only by AQT. No changes are made to the database when you define a user-defined key.
AQT will not check that the key is actually unique before you define it as a user-defined key. It is your responsibility to ensure that the key specified is unique.
AQT checks the user-defined keys before seeing if there is a primary key or unique key defined on the table. You may wish to define user-defined keys for the tables you deal with on a frequent basis - this will avoid the overhead of AQT having to access the database to check for primary key / unique key.

Editing your list of User-Defined Keys

To see the user-defined keys that have been defined for your database:

from the Database Explorer, go Tools > Edit list of Unique Keys
from the Data Display window, go Edit Data > Edit list of Unique Keys

User-defined keys are held in file key<dbname>.txt (example keyAQTDemo.txt) in your default directory. The file name is given in the caption on the User-Defined Keys window.

You can manipulate these files like normal files - edit, copy between machines etc.