SQL Power Loader Suite Version 4.0.0 - Release Notes

SQL Server

The SQL Power Loader can now read any source database and can load SQL Server in addition to Oracle.

Security Administration

We have tightened security to give you even more control over which users have what privileges in your SQL Power Loader Suite implementation. The new/improved group PL_ADMIN is a super-user with all application privileges. Only members of the PL_ADMIN group can modify object and system privileges. However, anyone with grant privileges on an object can grant other users access to that object.

The security explorer is easier to read now that all object/object type privileges are listed on one line, instead of having a parent and children.

We have also added new reports in the security area to assist you with understanding which privileges a user has. These reports link together object and system privileges for the user and all groups that the user belongs to. These screens (there is one for privileges and one for e-mail notifications) give you a complete list.

Performance Improvements

Engine performance has been improved significantly (over 230% from the previous release). Although performance will vary depending on the amount of the transformations, the speed of your netwrok and database server, we are now seeing extracts in excess of 100,000 records per minute, and loads in excess of 50,000 records per minute which is something to be excited about!

Data Mapping - General

Different outputs are now being shaded in different colours for increased visibility. This reduces the need for the option to alternate lines on grid screens, which has therefore been decommissioned.

Data Mapping - Sequences

You now have more options with sequences in the data mapping. When connected to an Oracle database, you may continue to use an Oracle sequence. You have the added ability to increment in memory. What this means is that the engine reads the database once, and doesn?t need to go to the database for each record being processed. After the transaction has completed, the sequence will be updated to reflect the maximum value we incremented.

Alternatively, you can perform sequential assignments by referencing a database table, instead of an Oracle sequence. The engine will get the maximum value in the selected column of the specified table. As above, you can increment in memory to avoid having to return to the database for each record being processed. The table can optionally be updated when the transaction completes.

Data Mapping - Default Values

Default values have been re-written. Although we still offer all common Oracle functions, we have added new functions from SQL Server and some of our own. These functions are all tuned for excellent performance and eliminate the need to access the database for each record processed.

Data Mapping - Transformation Criteria

A new step has been added to the data mapping. Remembering TGMSD, we now follow those steps with a 'transformation criteria'. The idea is that no matter which type of transformation was done (e.g., T or G or?), we may want to do an overall transformation to implement a business rule. For instance, we may have rules to do a translation, and then do a direct mapping if that fails. In either case, though, we want to convert the value to upper case and take only the first 10 characters. Formerly, we would have duplicated this processing in the translation and in a default value. Now, we can put it in the transformation criteria and have it apply to the value, whichever rule it results from.

Error Re-Processing

This component has been revamped. Now you no longer write errors to the database. Instead, the list of records in the bad file is the list of records that have not yet been successfully re-processed. The bad file grows with new errors, and shrinks when errors are successfully re-processed. Because of these changes, there is no longer an option to write errors to the database. There is no errors screen accessible by the application.

SQL Power Loader Suite Version 3.2.0 - Release Notes

Additional Auditing

A new auditing field has been added to all dictionary tables to keep track of the user who last made changes to meta-data. In this case we are keeping track of the operating system user; the database user also continues to be stored.

New to the SQL Power Loader Suite

Export

The export facility has been revamped to allow more flexibility in importing into a data dictionary that is at a different release. Exporting will prove useful both for moving SQL Power Loader Suite objects between disconnected databases and for remote support by SQL Power staff.

SQL Power Loader Improvements

Data Mapping

Variables are now being highlighted in red for increased visibility.

Job Statistics

On the job statistics screen, there are general statistics for each job component (job detail). If you double-click on a job detail statistic record for a pre-processing transaction or a package generated by the SQL Power Summarizer or SQL Power Matchmaker, you will now see more detailed statistics.

New to the SQL Power Loader

Replace and Analyze Tables

When your SQL Power Loader transaction loads a table, you can now choose to replace the contents before starting the load. Also, you may choose to analyze the table after the load, to improve query performance for your users.

SQL Power Summarizer Improvements

Thresholds

All screens referring to thresholds have been removed and now exist in a stand-alone application called SQL Power Dashboard Administration.

Measures/Dimensions

There is a new wizard to help you add dimensions and measures. Instead of individually selecting columns, you can multi-select columns.

New to the SQL Power Summarizer

E-Mail Notification

A new button on the submission screen allows you to create a script that will run the summary package and also check and process e-mail notifications based on the result.

Packages

Just as you can invoke transactions before and after a SQL Power Loader transaction, you can now invoke packages in the SQL Power Summarizer before or after the summary package, and also after aggregation (before continuous dimensions and cumulative measures are processed).

SQL Power Matchmaker Improvements

Validation Screen

This key screen was enhanced to allow much more flexibility to re-size, re-order and hide columns. Also, lots of filtering is allowed (by match percentage, match group, status or any free-formatted filter).

Input Filtering

You can now add filtering to pinpoint which records you want to find matches for in the database. This allows you to do things like only find matches where 1 record is type A and the other record is type B?.

Match Group Filtering

Each match group (a matching rule, essentially) can also have a filter. This allows you to specify which records each individual rule applies to.

New to the SQL Power Matchmaker

Variances

New fields on the match criteria screen let you do some creative matching. Instead of requiring a numeric field to match exactly, you can say that the 2 records must have amounts less than $1 apart, for example.

E-Mail Notification

A new button on the submission screen allows you to create a script that will run the match/merge package and also check and process e-mail notifications based on the result.

SQL Power Loader Suite Version 3.1.0 - Release Notes

Column Mapping

The correct specification of the primary key (formerly called natural identifier) is important to the success of your transaction. We will now default it for you, based on the primary key in the database. Also, the column mapping records that are marked as part of the primary key will be highlighted in a different colour.

We know that it can be easy to delete column mapping records by mistake. We have added an "Are you sure?" message to help ensure that this will no longer be a problem.

Action Types

Previously, the action to be performed in the transaction was specified on the submission screen and the same action would be performed on each input record. (NOTE: this describes a normal case; the action could also refer to an input column and the column value would determine the action performed on each input record) Now, you may also specify the action for each output table. This allows you to be more precise and may improve performance. You can even set the action for output2 to be the action eventually determined for output1 (i.e., inherit).

Job Details

In addition to transactions, delta generations, jobs, procedures and functions, you can now run batch scripts, programs (any executables) and SQL scripts as a job step. You can also run a SQL Power Summarizer summary package, or a SQL Power Matchmaker match or merge package.

There is a new wizard to help you add components to a job. Instead of individually selecting items to add to the job, you can multi-select groups of items.

Release Details

As with the job details, there is a new wizard to help you add components to a release. Instead of individually selecting items to add to the release, you can multi-select groups of items.

Statistics

In addition to being written to the log file, Delta*Generator statistics are now written to the database and can be viewed from the front-end.

Any packages that are run pre- or post-transaction now have statistics written to the database. The transaction statistics screen is split, showing the transaction statistics at the top of the screen, and any package statistics at the bottom of the screen.

We have also added new statistics:

• 1) total time spent on adds, updates and deletes
• 2) transformation statistics counts and times spent on each transformation step in the column mapping processing

Package

On the package screens, you can now view and edit the package code (subject to database permissions). This is also available on the job details screen for procedure/function objects.

Inactive Rules

New check boxes on grids allow you to de-activate transaction rules. In effect, you can comment out column mapping records, as well as exception handles, packages, and job details.

INI File

Formerly you had to edit the ".ini" files to configure certain aspects of your SQL Power Loader Suite environment. Now the SQL*Plus path, exception handles, and date formats are configured from the Tools/Administration menu. The "plset.ini" file is no longer used, but the "pl.ini" file remains for storage of database connection information, it should not be directly edited.

New to the SQL Power Loader

E-Mail Notification

New administration modules allow you to designate users to receive e-mails based on the status of a SQL Power Loader Suite item (e.g., transaction, job, summary). E-mails can be sent on failure, warning or success (i.e., completion). Check the option on the submission screen to indicate that e-mails are to be sent, as required, at completion of the transaction or job.

Loading New Tables

When you build a load transaction, it is now possible to specify a table that does not yet exist. Once you have completed the Column Mapping, we can help you create the new table. Alternatively, if the table exists, but you have new information that needs to go into it, we can help you build ?alter? statements to add columns to your existing table.

Cross-Tabular Files

The SQL Power Loader can now handle cross-tabular file (i.e., where there is a column heading, a row heading, and a value in the intersection) as input to a transaction. Pick the "Cross-Tab" file type on the input/output table/file screen.

XML Files

The SQL Power Loader can now handle XML files as input to or output from a transaction. Pick the "XML" file type on the input/output table/file screen.

Extraction Wizard

It is now easier to create a transaction for a simple data extraction. Follow the steps in the extraction wizard (under the Tools/Wizards menu).

Web Reports

Our new Web reports allow you to view the meta-data for each SQL Power Loader Suite object: transaction, job, release, summary, match/merge. There are also statistics reports. Click on the reports button on the toolbar in each application to access these reports.

SQL Power Summarizer Improvements

Submission Screen

A new submission screen allows you to Generate, View, Compile and Execute the summary package. You can also specify run-time parameters (including new rollback segment, debug mode and process count parameters). From this screen you can also access statistics (new to this release) and the log file (new to this release), and view the output summary table contents.

Inactive Rules

New check boxes on grids allow you to de-activate transaction rules. In effect, you can comment out summary dimensions and measures.

Cumulative Functions

This functionality allows you to aggregate summary data over time. Previously the data was aggregated from the beginning of time. Now you can specify a cumulative frequency to calculate and store year-to-date (month-to-date?) totals. New functions allow you to do cumulative MAX and MIN, in addition to the original SUM and COUNT functions.

New to the SQL Power Summarizer

Break

The resulting summary package may summarize large amounts of data, and require large amounts of rollback and temp space in the database. You can now specify how to break up the summarization into chunks, thus avoiding database space problems.

Progress Screen

When you execute a summary package, you will be shown a new screen that charts the progress of the package (based on the number of breaks processed).

Thresholds

The SQL Power Summarizer is tightly coupled with the SQL Power Dashboard. You need to specify thresholds to flag the summarized data as red, yellow or green. You do this manually on the new Threshold screen, or you can import from a spreadsheet with the new Threshold Import Wizard (accessible from the Tools/Wizards menu)

SQL Power Loader Suite Version 3.0.0 - Release Notes

Improvements

Column Mapping

We have gone to great lengths to make this screen easier to use:

• The columns are put in TGMSD order
• The editor windows for group function and translation have been improved
• The translation is no longer split into 3 fields and is therefore easier to understand
• Variables show up in the list of input columns once you have defined them
• The list of input columns indicates which columns have already been mapped and which ones have not yet been mapped

File Format

It is easier to set up file formats and parse to validate them:

• There is a new mini-parser that shows the first lines of your input file, split into columns based on your file format. It dynamically changes to reflect changes that you make to the file format.
• The mini-parser and the original, full-blown parser, show the column data format as well as the starting position and length of the column (for fixed length files).
• An extra column at the end shows you any unmapped data.
• We can now default fixed-length file formats from a header record (for an input file), or from an input table (for an output file)

Input/Output Tables/Files

The SQL Power Loader does not accept multiple inputs, so we usually suggest that multiple input tables be joined via a view. Now, you can define and build this view from the front-end.

When you define an input/output file, the file name is defaulted based on the file id, and its attributes are also defaulted (to ASCII, variable length, comma-separated, "-delimited)

Explorer

The transaction explorer has had a face-lift. Before, there were two separate explorers in the application (one for transactions, one for summaries). Now, there is one explorer that has many different views: transactions, jobs, releases, summaries, administration and All (which shows all objects, a combination of all the explorers except the administration one).

If you duplicate a transaction, we attempt to update the log file names to refer to the new transaction id.

Under each input/output file for your transaction, you see a link to the file (double-clicking shows the file contents). Under each input/output table, there is a link to the table (double-clicking takes you to the Database Structure screen). Previously, if there were output files (i.e., we had an extract transaction), they appeared in the "Output Files" section of the explorer. Now, they show under each of the transaction's output files.

Login

Now, when you select "Refresh" from the "Database" menu, the INI file is reloaded. Before, after making any changes, you had to exit and re-enter the application.

Submission Screen

When you run the engine, you can now choose to append to the log, error and bad files (instead of overwriting them).

There is a new button, "Show Command", which will show you the command that is invoking the engine. You can save this to a file, if desired, for inclusion in a script.

Engine Notes

Log files are much easier to read now. They show you the engine command issued, the raw input records, the filtering that is done, the execution time for packages... Instead of a list of SQL statements, each is labeled so you know which are group functions, translations...

You can now specify the input file name at the command line.

Exception Handling

You can invoke a PL/SQL procedure on the exception handling screen.

There is a new button to bring back the default exception handles.

Packages

We now allow you to run multiple transaction-level packages, through use of a sequencer. Also, you can view the package contents and the package parameters from the front-end.

Miscellaneous

We have made great strides to make it easier to run your transactions in UNIX. Each spot where you define file names (e.g., input/output files, log files) now has a spot for a UNIX file name too. When the engine runs, it will pick the appropriate file to use, based on what operating system you are using.

The Database Structure screen now shows which columns are NOT NULL.

The old concept of "Inter-Database Copy" has been changed to the concept of "Migrate". You no longer need a database link, but instead maintain two different database connections through the SQL Power Loader.

New to the SQL Power Loader Suite

ODBC Compliancy

We are now ODBC-compliant and allow you to read from many different source databases, through ODBC. We do not yet load into these databases.

Jobs

There is a new concept of jobs. A job is a set of tasks to be performed and may include:

• running transactions or other jobs
• running the delta generator
• executing PL/SQL procedures or functions

Job details can be inter-dependant; you can set it up so that if the first transaction fails, the whole job aborts. You can generate job scripts from the front-end which can be run in NT and UNIX.

There are new screens for defining the job header and job details, as well as a submission screen and a stats screen.

Releases

Another new concept with version 3.0 is releases. This allows you to create a set of items that together form a release. This can include:

• transactions
• jobs
• views
• SQL scripts
• packages

We then assist you with creating backups as well as with migrating these items to another database.

There are new screens for defining the release header and release details. A log file, viewable from the explorer, is created as a result of the migration.

Backup/Restore

To assist with source control, we have added the ability to backup transactions, jobs and releases. Doing so will make a copy of the meta-data and save it to a different section of the explorer. It will be read-only (it can be run, but not modified). If you wish to modify it, you can restore it. Backing up a job or release will not only duplicate the job/release meta-data, but the meta-data for the job/release contents (each transaction/job in the job/release will be duplicated and can be restored individually).

Admin

We have implemented security. You can set up groups of users with different privileges. Users and groups can be given high-level privileges (ability to delete any transactions, modify any jobs...) or object-level privileges (ability to delete the transaction x, modify the job y...).

SQL Power Loader Suite Version 2.7.0 - Release Notes

Ease of Use

It is now much easier in many cases to set up a SQL Power Loader transaction. On the input file screen you can check a box to indicate that your input file has a header record. If you select this option and go to the file format screen, you have the option of deriving the file format from the header record in your file. Output file formats can be derived from the input table/file. Even the column mapping can be derived, provided the input and output column names match, which will often be the case if you default the file formats.

A similar indicator has been added for output files. If you check the option to "Export a header record" on the output file screen, when the SQL Power Loader engine is run, an extra record will be added to the output file with the column headers from your file format.

Column Mapping

The column mapping screen has had a face-lift! The screen is now split, similar to an Excel spreadsheet, to allow you to more easily see the transformations associated with a particular output column. The processing sequence, input table/file id, input column, output table/file id and output column are displayed on the left hand side of the split. The remaining columns are on the right hand side. You can change the size of the split by clicking on the black rectangle at the bottom of the grid underneath the split line (the cursor will change) and dragging. Your split re-sizing will be remembered within each session (e.g., if you make the left split smaller, the next time you open the column mapping screen, the left split will be this smaller size).

The record on the column mapping screen are now sorted in a different order. If you have multiple outputs, the mapping records are sorted by the processing sequence number that you defined on the output table/file screen. Within each output table/file, the records are sorted by the processing sequence you define on the column mapping screen, then by column name.

You may now choose to alternate the lines on the column mapping screen, that is to show even lines in a different colour than odd lines. This may make the screen easier to read. To choose this option, use the "Tools", "Options" menu. On the "Preferences" tab, check the option for "Alternate Lines on Column Mapping".

The syntax checking for default values, group functions and translations is now more accurate than before. This is intended to help find problems before running the engine, however it does not always return the same results as the engine (it may find an error when the engine will not, or may not find an error that the engine finds).

Drop-down lists that show input column names (default value editor and translation value editor) now also show variables to make it easier to construct your transformation rules.

There is a new button to clear the entire column mapping.

File Format

Deleting on this and other screens is now easier. You may now click on the column header and delete. Repeated pressing of the delete key will delete subsequent records, there is no need to select another record first.

There is a new button to clear the entire file layout.

When defaulting a file format based on an input file header record, any column names that contain spaces will have those spaces converted to underscores.

If you rename a column, you will get a warning if that column is referenced in the column mapping. If you proceed with the rename, column mapping references will be updated.

Transaction Explorer

The transaction explorer had a face-lift as well. You will notice that its appearance has changed. Its performance has improved as well - when the explorer is first brought up, it is much faster than before.

The "Tables/Files" menu (what you see when you right mouse-click on an input or output table or file) now contains packages, stats and errors, to match what is in the explorer.

There are now stats and errors at the transaction level.

Delta Processing

A new component has been added to the SQL Power Loader Suite. The Delta*Generator takes an old and a new input file and sorts them based on a primary key (which you define on the file format screen). It then compares the two files and produces an output file with the records that have changed between the two files, be it adds, updates or deletes. This output file also has an extra column (which is automatically added to your file format) to indicate this delta action. When you run the SQL Power Loader engine, you can indicate this new column to be read to decide the action type for each record. You can indicate on the input file screen that the input file is "Delta Generated". Clicking on this button will take you to a screen where you can define the Delta*Generator run parameters and even run the Delta*Generator.

Login

The login process has been improved. When you login to the SQL Power Loader, a local Access database, containing links to the SQL Power Loader dictionary tables, is created. On subsequent logins, instead of refreshing this local database, it will be re-used, provided that you login to the same account. If you log on as a different user, with a different default schema owner or to a different, or modified, database connection, this snapshot will be refreshed. You can force a full login by selecting "Full Login" from the "Database" menu. Any time the SQL Power Loader dictionary tables change, the snapshot needs to be refreshed. This means that any time you upgrade to a new release, a "Full Login" should be done.

When defining a database connection, the "ODBC Data Source" is now a drop-down with all the ODBC data sources you have defined.

Help

The Help file has been revamped. There are now links between documents. There is also a Table of Contents which allows you to jump from page to page and see related topics. Selecting "Help Topics" from the "Help" menu will bring up the introductory help page.

Submission Screen

The "Debug Mode" and "Write Errors to DB" options are mutually exclusive. If you check one, the other is automatically de-selected. This is because debug mode does a rollback, whereas when you are writing errors to the database, you must commit.

"Write Errors to DB" is not available if you are extracting (i.e., all your outputs are files).

If you select a processing sequence of "Error", "Error, Regular" or "Regular, Error", the "Write Errors to DB" option is automatically selected for you. If you pick "Regular", the option is automatically de-selected. Vice versa, if you select the "Write Errors to DB" option, a processing sequence of "Error, Regular" is selected. If you de-select the option, "Regular" is selected.

If you select a processing sequence of "Error", "Error, Regular" or "Regular, Error", the "Max retries" field defaults to 5.

You may now name a rollback segment to be used for a transaction. This will be useful if you have a large job where you don't want to commit until completion. A drop-down list shows all on-line segments in "dba_rollback_segs". If you don't have access to this system view, you may type in a rollback segment name. If the rollback segment cannot be found, or is not specified, the default segment will be used.

The log, error and bad file names can now be maintained. They default to the transaction id with extensions as specified on the "Database - Preferences" screen (accessed from the "File" menu).

After each submission, the submission parameters are saved to the database. Next time you submit the same transaction from the front-end, the same parameters will be used by default.

Engine Notes

When the engine starts running, the start message printed to standard out (and the log file) now indicates if we are in Debug Mode.

If the Oracle error 3114 (not connected to Oracle) is encountered, a message is written to the log file and the transaction is aborted.

When an exception handle is used, a message is written to the debug log file.

The statistics captured by the engine have changed. For input statistics, it now captures "Total Input", "Skipped" (uses the skip parameter on the submission screen), "Read", "Filtered Out" (uses the filter on the input table/file screen), "Processed". The "Total Input" is the sum of "Skipped" plus "Read". Also, "Filtered Out" plus "Processed" equals "Read". Output statistics remain the same: "Process", "Reject", "Skip" (any records that are skipped due to an exception handle), "Add", "Update", "Delete". The number of output records in "Process" should match the number of input records "Processed".

Statistics

The new input statistics are now displayed in addition to the output statistics. You can choose to view both or either set.

Certain submission parameters are saved with the statistics to help you measure the performance differences for each set of parameters.

There is a new status field. If the status is "Success", there were no errors, "Warning" had some errors, "Failed" had enough errors to hit a threshold and the transaction aborted.

Miscellaneous

You may now process Tab-delimited files. You can pick "Tab" from the drop-down in the delimiter column on the input/output table/file screen. You can still use any other, single-character delimiter by typing it in this field.

The transaction id can now be up to 35 characters long.

The filter field has been changed from 500 to 2000 characters.

The package name field has been changed from 35 to 200 characters.

The package parameters field has been changed to 2000 characters.

The default value, group function, and translation "Where" clause are all 2000 characters now.

The transaction description has changed to 255 characters.

The input/output table/file screen has a new comment field.

Two new columns have been added to each SQL Power Loader dictionary table: "last_update_date", "last_update_user". These are populated by triggers each time a record is modified, with the intention of being used for auditing purposes.

Bug Fixes

Some installations had problems running the SQL Power Loader engine unless the executable was in a directory without spaces (e.g., c:PowerLoaderSuite instead of "c:Program FilesPower Loader Suite"). Now the executable is called surrounded by quotes so that a file path containing spaces can be referenced. The same is done for output files.

If you view the parameters for a package, then close the screen with the system close button (the "x" in the upper right corner), the parameters are no longer deleted.

SQL Power Summarizer

Previously your summary table had to exist before you set up a summary transaction. The "Target Column" columns on the dimension and measure screens was a drop-down with all the columns in this summary table. Now you have the choice to construct the summary table from the dimension and measures that you define. There will be no drop-down for the "Target Column" since the summary table does not exist, but it will default to the source column. You may then click on "Create Summary Table" to build the summary table. You will be shown the DDL which you can modify before running. You also have the option of saving the DDL to a file.

To help you build your join clause, there is now a "Summary Join Constructor" screen.

The source calculation has been increased to 2000 characters.

Comment fields have been added to the summary header screen and both the dimension and measure screens.

SQL Power Loader Suite Version 2.6.3 - Release Notes

Default Values

On the column mapping screen, there is a "Test" button for default value (before this button was only for group function and translation). If the syntax check finds an error, the error message will show you the statement that it parsed. This should help track down errors.

We believe we have resolved the confusion about the use of the default value transformation. Default values should be used for constant values (e.g., 0, "POWER LOADER", SYSDATE) as well as for derived values (e.g., UPPER(':input_table.input_column'), LAST_DAY(SYSDATE)). The default value does a "Select From DUAL". These derived values were formerly best accomplished with a group function. One thing to remember is the order of operations: Translation, Group function, Mapping, Sequence, Default Value. Because direct mappings are done before default values, if you want to upper case something in a default value, you should remove the direct mapping or it will be done first and the upper case conversion will never be used.

Check for Natural Identifiers

SQL Power Loader transactions cannot run if there is not a natural identifier on each output. Before the engine is invoked, there is now a check in the front-end to determine if each output has a natural identifier.

Ease of Use

On the column mapping screen, in order to set a column as being part of the natural identifier, you had to click twice in the natural identifier column. Now, you only have to click once. The same is true for those columns with edit windows (default value, group function, input translation column); clicking on the column will immediately bring up the edit window.

The list of valid actions has been diminished. When you start, the default is "Add, Update", since it makes sense in most cases to allow additions and modifications to each output column. However, when you check off a column as a natural identifier, the valid action defaults to "Add" only (it doesn't make sense to update the columns that form the key).

On the file format screen, in some cases the processing sequence was not automatically generated for you. The processing sequence is now generated in all cases. In addition, the starting position (used for fixed length files) is defaulted when you create a new record instead of waiting until you click on the starting position column.

Login Options

Logging into the SQL Power Loader can take a significant amount of time if the database you are connecting to has a large dictionary of objects. There are now two login options, found in the "Tools", "Options" menu on the "Database" tab. If you select "Database Schemas", "All", when you connect a snapshot of all objects is taken (the Oracle system tables all_objects and all_tab_columns are used). If you choose "Selective", the login snapshot uses views called "pl_objects" and "pl_tab_columns" that you must create. This allows you to create a view on all_objects while specifying particular owner(s) whose objects you want to have access to. This list of objects created from the login snapshot is used in drop-down lists (for input/output table, for example) and elsewhere throughout the SQL Power Loader (e.g., the database structure screen).

Exception Handling

A new exception, "Others", has been added to the list. This exception is invoked when there is an exception that has not been trapped by another exception handle.

Engine Notes

If you tell the engine to skip n records, and you also have a filter on the input records (e.g., only process those records that meet a certain criteria), the n records are skipped with no regard to the filter. After the skipped records, the filter is applied to each record to determine whether or not to process it.

If you specify thresholds (i.e., maximum records to be added, updated, deleted), you may wish to use different thresholds for different outputs. For instance, it may be acceptable to delete n child records, but only n/2 parent records. Once the first threshold is violated, the engine will abort the transaction. A rollback will occur to the last commit. If you do this kind of processing, you may wish to have a commit frequency of 0 so that all changes are committed at the end of the job. In this case, all changes made before the threshold violation can be rolled back.

Many of you have probably seen the error message "Can't read trans_col_map" when trying to run a transaction. This error message has been improved upon to give you more information about the error.

When you show progress messages after every n records, the text written to standard output now includes the number of records rejected. Instead of waiting until the transaction finishes to find that all records were rejected, you will now know after the first progress message and can cancel the job (by closing the DOS window).

The statistics shown after the engine runs have input records processed as well as output records processed. The number of input records processed is the number of records examined by the engine (regardless of whether they pass the filter criteria). This is restricted by the submission screen (and run-time) parameter for number of records to process. The number of output records processed doesn't include the records that were skipped. This is restricted by the thresholds for maximum adds, updates, deletes.

The log file that you see as the result of debugging now has timing to assist you in tuning your transaction.

Website

We are striving to make our website more useful for our clients. Work is underway to add a new section to the website for clients only. You will be able to submit suggestions, see release notes, access a list of frequently asked questions... We will distribute more information when "renovations" are complete.

SQL Power Loader Suite Version 2.6.0 - Release Notes

Error Processing

There are now two types of processing: Regular and Error. They can be performed individually or in sequence. Error processing uses a new value, the maximum retry count, to determine how many times to attempt re-processing of each error record before abandoning the effort. The processing type and maximum retry count can be specified on the transaction header screen or overwritten at run-time on the submission screen (or through the command line).

Error screens in the front-end show the error status (FAILED, SUCCESS), the retry count and the last date the error record was retried. There are new delete buttons to delete all errors or all errors with a status of SUCCESS. Records can also be individually deleted with the Delete key.

The statistics screen shows the type of processing (ERROR or REGULAR). It also has a new button to delete all records. They can also be deleted individually with the Delete key.

Exception Handling

Since there are standard exceptions that are used in the majority of cases, 3 exception handles are automatically created with each transaction:

• If a unique constraint violation occurs on an add, change to an update.
• If no data found on an update, change to an add.
• If no data found on a delete, skip.

New Run-Time Parameter

When errors occur at run-time, they are written to an error table in the database. This allows you to view them from the front-end (instead of from the BAD file) and to re-process them. However, it entails some overhead in writing to the database and can slow down your transaction. You can now specify that you don't want errors written to the error table (they will still be written to the BAD file).

More Defaulting

On the file format screen, the column data type now defaults to Varchar.

When defining an input or output table or file, the processing sequence number is mandatory. Instead of showing you an error message if you don't define it, the next available processing sequence number is selected from the database.

Syntax Checking

On the column mapping screen, if you use the "Test" button to check the syntax for a translation or group function, you will now get a message on successful or unsuccessful validation (instead of just the latter).

On-Line Help

The first release of on-line help has been implemented. Currently, there is no table of contents and no searching capability. There are also no links between pages. However, there is a context-sensitive help screen for each screen in the application. This can be accessed using the help button on the toolbar. In the case of modal windows, where you cannot click on the toolbar, there is a "Help" button on each screen.

SQL Power Loader Suite Version 2.5.0 - Release Notes

File Filtering

Release 2.50 of the SQL Power Loader Suite allows the user to specify filtering criterion for files. In the event that only a subset of the input records need to be loaded, one can specify the input column_name to filter on and the associated filtering value (similar to a where clause).

Transaction Explorer

More new items have been added to the explorer window. In addition to being able to open transaction output files (log, err and bad files) from the explorer (available in release 2.4.0), you can now view the files that are created as a result of an extract transaction.

Copying Summaries Between Databases

In release 2.4.0 it was possible to copy transactions from one database to another. That has been expanded to include the copying of summaries. If you have a summary defined in one database, you can now copy it to another database through use of a database link. Create a database link in the target database going to the source database. Run the SQL Power Loader Suite, logging on to the target database. Choose the Inter-Database Copy menu item.

Packaged Procedures

Formerly the SQL Power Loader was only able to handle invoking PL/SQL functions, not procedures. Now, through use of an additional field, both procedures and functions can be invoked. You will find an additional drop-down on the package screens (both at the transaction level and at the table/file level) which lets you indicate whether the code you are invoking is a function or procedure.

Maximum Errors

Previously, you could specify thresholds for the maximum number of adds, updates or deletes to perform. That functionality is now expanded and allows you to enter a maximum number of errors. If this maximum number of errors is exceeded, a message will be written to the log file to indicate that this is the case, and the transaction will abort.

Show Processing Progress

In Previous releases of the SQL Power Loader Suite, one had to wait until the Data Extract and/or Load is complete before viewing any of the timing or processing statistics. In 2.50 a parameter can be passed to the Engine SHOW_PROGRESS=N, where N is the frequency of the Progress Update; if N is set to 50 then the engine will log a record every time increments of 50 records have been processed.

User-Friendliness

Several changes have been implemented to add make the SQL Power Loader Suite easier to use.

From the front-end, you will now be shown a message if you try to access the Column Mapping for a transaction without first specifying at least one input and one output. In the same vein, if you try to access the SQL Power Loader submission screen without first specifying a column mapping, you will be prompted to do so.

From the back-end, you will be now shown a message when the SQL Power Loader starts up to show you the start time and let you know that it is working. Also, before invoking a transaction-level package, a message is displayed. Any time you have an input value that is too long to fit in the specified output column, instead of generating an error message and not processing the record, you will now find that an automatic truncation takes place and a warning message is given in the log file.

SQL Power Loader Suite Version 2.4.0 Release Notes

Column Mapping

When you click on a new line, if there is only one input or output file, it is pre-selected in the drop-down. An action type of "Add, Update, Delete" is also pre-selected. Since the output columns can only be specified once, they are now removed from the drop-down list once they have been used (i.e., the list diminishes as you fill in your column mapping).

Productivity is increased due to the front-end doing more validation so that it finds potential problems instead of discovering the issues when the back-end process runs. Group functions and translations now have syntax checking.

Exception Handling

Common exceptions are now listed in a drop-down so you don't have to remember Oracle error numbers.

File Format

Another enhancement to improve productivity is the copying of file formats. You can now select an existing file from a drop-down list and choose to copy its format.

Transaction Explorer

New items have been added to the explorer window. You can now open transaction output files (log, err and bad files) from the explorer. Skeleton exception and package information can be viewed directly from the explorer, instead of opening another window.

Performance has improved due to new context-sensitive behavior. When you select "Expand" or "Collapse", the action applies to those items below the selected explorer level. A "Refresh" applies only to the selected transaction (unless your context is the "Transactions" header record in the explorer, in which case all transactions are refreshed).

Database Refresh

If you change the database definition while running the SQL Power Loader Suite, you can now refresh the connection to the database without logging out and logging back in.

Copying Transactions Between Databases

If you have a transaction defined in one database, you can now copy it to another database. This is done through a database link. Create a database link in the target database going to the source database. Run the SQL Power Loader Suite, logging on to the target database. Choose the Inter-Database Copy menu item.