Load Tables

Load tables once defined would normally be created and loaded, unless these actions were performed as part of the drag and drop operation.
The menu below shows the operations that can be performed on Load tables.

Note

WhereScape RED supports the use of keyboard shortcuts—the underlined letter of a menu option. For example, pressing P opens the Properties window of the selected table, pressing L performs an interactive load, etc. Ensure that the Windows > Control Panel > Ease of Access setting associated with keyboard shortcuts is enabled, to display keyboard shortcuts in RED. Refer to the relevant MS Windows documentation for details.

Menu Options

Description

Properties

Displays the Properties window for the Load table, albeit focused on different tabs within this window.

Storage

Displays the Properties window for the Load table, albeit focused on different tabs within this window.

Source

Displays the Properties window for the Load table, albeit focused on different tabs within this window.

Display Columns

Displays columns of the load table.

Display Indexes

Displays indexes of the load table.

Display Data

Displays data of the load table.

TIp

If the data is displayed, only the first 100 rows are returned from the table. Either the SQL Admin tool (accessible via the WhereScape start menu option), or the Excel query must be used if more detailed data analysis is required.

Query via Excel

Click to query columns in Microsoft Excel.

Tip

When a column list has been displayed in the central pane, it is sorted based on the order field associated with each column. Clicking the column label Col name will sort the columns into alphabetical order. A subsequent click will re-sort based on the order field.

Add Column

Select this option to add new columns. Normally columns can be added via drag and drop.

Add Index

Select this option to add new indexes. Normally indexes are created during the procedure generation phase.

Regenerate Indexes

Select this option to add missing standard indexes. Selecting this option displays a window with options to regenerate missing indexes in the metadata and recreate them or to just regenerate the missing indexes in the metadata.

Relationships

Select this option to manage enhanced relationships. Select this option to perform one of the following:

Add Relationship - Displays the Add Relationships dialog.
List Relationships - Displays a list of enhanced relationships in the Drop Target Pane for the selected object.
Generate Relationships - Generates relationships which have not yet been defined in the metadata

Impact of Change to Table

Select this option to produce a list of objects that will be potentially impacted by a change to the load table structure.

Change Column(s)

Select this option to apply changes to a selected number of columns

Validate for Reserve Words

Select this option to produce a list of table or column names where reserved words have been used; enabled for supported ODBC Drivers.

Validate Against the database

Select this option to compare the metadata for the load table with the physical table resident in the database, and where required the table is altered to match the metadata.

Update Comments

Select this option to refresh table and column comments on the table, using the table's description and columns' business definition.

Note

You can manage the table and column comments outside the data warehouse environment via the Table and Column Comments menu option.

Gather Statistics

Select this option to gather statistics on a table. This action enables the underlying database to optimize each query based on the statistics collected about the data that is being accessed. Select this option to perform one of the following:

Perform Full Statistics
Perform Quick (Sample) Statistics

Gathering statistics can be performed on any table by selecting this option from a table's right click menu, or to automate this process, by adding a statistics task to a job being processed by the scheduler (Stats, Quick Stats, Analyze or Quick Analyze). For more information about adding statistics tasks to jobs for SQL Server, Oracle and DB2 refer to Editing Tasks in a Job.

Tip

Oracle has the statistics option in the Options window to save a generic statistics statement for analyze, quick analyze, stats and quick stats. If there is no statistics statement saved then a default statistics statement is used. The statistics process from the object context menu for tables is described below:

Oracle Tables
Perform Full Statistics	BEGIN dbms_stats.gather_table_stats(ownname=>'schema', tabname=>'table' , cascade=>TRUE); END;
Perform Quick (Sample) Statistics	BEGIN dbms_stats.gather_table_stats(ownname=>'schema', tabname=>'table' , estimate_percent=>'3' , cascade=>TRUE); END;
Perform Full Analyze	ANALYZE TABLE schema.table COMPUTE STATISTICS
Perform Quick (Sample) Analyze	ANALYZE TABLE schema.table ESTIMATE STATISTICS SAMPLE 3 PERCENT
SQL Server Tables
Perform Full Statistics	UPDATE STATISTICS schema.table WITH FULLSCAN
Perform Quick (Sample) Statistics	UPDATE STATISTICS schema.table WITH SAMPLE 3 PERCENT
DB2 Tables
Perform Full Statistics	RUNSTATS ON TABLE schema.table WITH DISTRIBUTION AND DETAILED INDEXES ALL
Perform Quick (Sample) Statistics	RUNSTATS ON TABLE schema.table
Perform Full Analyze	RUNSTATS ON TABLE schema.table WITH DISTRIBUTION AND DETAILED INDEXES ALL
Perform Quick (Sample) Analyze	RUNSTATS ON TABLE schema.table

Oracle Gather Statistics context menu example:

Perform Full Statistics
Perform Quick (Sample) Statistics
Perform Full Analyze
Perform Quick (Sample) Analyze
To add specific commands for Oracle statistics performed by the scheduler, refer to Statistics for details.

Version Control

A version of a Load table is a copy of the metadata definition of the table at the time of the versioning. This version information can be used to create a new Load table or can simply be left as a backup and reference point. Select this option to perform one of the following:

New Version - Enables you to create a new version of the selected Load table.
Build Application - Enables you to build an application file for the selected Load table.
Duplicate Object - Enables you to create a new Load table as a duplicate of the selected table.
Revert to Version - Enables you to revert to a previous version of the selected Load table. Displays a list of the available versions of the selected Load table, from which you can select and revert.

TIp

The extended property values set in the original Load table is copied to the duplicate Load table. Refer to Extended Properties for details.

Create (ReCreate)

Select this option to create the table in the database based on the definition stored in the metadata. To alter a table select the Validate against database option. Refer to section on table validation for details.

Truncate

Select this option to truncate the table.

Change Connect/ Schema

This option enables the rapid changing of the Connection information associated with the Load table. This information can be changed in-bulk for a number of Load tables. Refer to Changing Load Connection and Schema for details.

Delete Metadata and Drop Table

Select this to option perform one of the following delete options:

Delete metadata and drop object - This option deletes the metadata definition for the table and drops the table object from the database (default). This is a permanent delete and no recovery is provided, use with caution.
Delete metadata only - This option deletes the metadata definition for the table.

A version of the object's metadata is normally auto created (depends on the settings in Tools > Options > Metadata Versioning .

Load

Select this option to perform an interactive load of the data. The method of loading depends on the type of connection. This menu option is intended for use with small data volumes as in a prototype environment. Large data volumes would normally be scheduled. The Load locks the WhereScape RED screen until completed.

Tip

For ODBC based loads in an Oracle data warehouse, this interactive load does not use Direct Path loading so will be slower than a scheduled load.
The load option does not drop or create any indexes. Use the Process option if indexes need to be maintained.

Process Table via Scheduler

Select this option to send a request to the Scheduler to immediately process the Load table. This process drops any indexes marked as pre_drop, load the data and rebuild any required indexes. Control is immediately returned to the user and the loading occurs via the Scheduler.

Documentation

Select this option to generate (or read if already generated) the WhereScape RED HTML documentation for the selected object. Select this option to perform one of the following:

Display the documentation available for the selected object. Refer to Reading the Documentation for details.
Create the documentation for the selected object. Refer to Creating Documentation for details.

Projects

Select this option to perform one of the following:

Remove from Project - Select to remove the Load table from a project. Refer to Removing Objects from Projects for details.
Add to Project - Select to add the Load table to a project. Refer to Adding Objects to Projects for details.
List Projects - Displays a list of projects which contain the current object, results are shown in the bottom pane. Refer to List Project Memberships for Objects for details.

Note

Multiple objects can be selected by double-clicking the Object Group icon in the left pane and then Ctrl + clicking multiple objects in the Drop Target (middle) pane.
If there aren't any projects in the repository, the above options are unavailable.

Check Out

Select this option to check out the object for editing and prevent any other users from being able to modify, update or delete any of their associated objects while you are making changes. Refer to Object Check-Outs and Check-Ins for details.

Impact

Select this option to produce a number of reports and diagrams. Select this option to perform one of the following:

Track Back Report
Track Forward Report
Track Back Diagram
Track Forward Diagram
Dependent Jobs Report

Code

Select this option to view a procedure associated to a table or to regenerate the table's update procedure. Select this option to perform one of the following:

View Load script
View Post Load script
Regenerate Load script
Rebuild Post Load script
Regenerate Post Load script

Note

Only Load tables with one or more defined procedures have the Code view option.
If the Load table is Script-based load type, either option is displayed in the Code sub menu:
- Generate Load Script (if a script is not yet generated)
- Regenerate <script name> (if a script has already been generated).

Refer to Script based load for details.

Build

Select this option to build a Scheduler job or an application based on the Load table(s) selected from the middle pane. Refer to Building Scheduler Jobs from Object Groups and Building Applications from Object Groups for details. This option is available from the context menu of Load tables listed in the middle pane.

Content

Space Tools