SQL Execute

CHNGPGS_THRESH

Asynchronous page cleaners will write changed pages from the buffer pool (or the buffer pools) to disk before the space in the buffer pool is required by a database agent. As a result, database agents should not have to wait for changed pages to be written out so that they might use the space in the buffer pool. This improves overall performance of the database applications.

You can use this parameter to specify the level (percentage) of changed pages at which the asynchronous page cleaners will be started, if they are not currently active. When the page cleaners are started, they will build a list of the pages to write to disk. Once they have completed writing those pages to disk, they will become inactive again and wait for the next trigger to start.

In a read-only (for example, query) environment, these page cleaners are not used.

Recommendation: For databases with a heavy update transaction workload, you can generally ensure that there are enough clean pages in the buffer pool by setting the parameter value to be equal-to or less-than the default value. A percentage larger than the default can help performance if your database has a small number of very large tables.

DB2_CAPTURE_LOCKTIMEOUT

When set to 'ON', the lock timeout reporting function captures information about lock timeout events, including information about the key applications involved in the lock contention that resulted in the lock timeout.

DB2_EVALUNCOMMITTED

With this variable enabled, predicate evaluation can occur on uncommitted data. This means that a row that contains an uncommitted update may not satisfy the query, whereas if the predicate evaluation waited until the updated transaction completed, the row may satisfy the query. Additionally, uncommitted deleted rows are skipped during table scans. DB2 will skip deleted keys in type-2 index scans if the DB2_SKIPDELETED registry variable is enabled.

DB2_HASH_JOIN

Specifies hash join as a possible join method when compiling an access plan. DB2_HASH_JOIN needs to be tuned to get the best performance. Hash join performance is best if you can avoid hash loops and overflow to disk. To tune hash join performance, estimate the maximum amount of memory available for the sheapthres configuration parameter, and then tune the sortheap configuration parameter. Increase its value until you avoid as many hash loops and disk overflows as possible, but do not reach the limit specified by the sheapthres configuration parameter.

DB2_INLIST_TO_NLJN

Sometimes the optimizer does not have accurate information to determine the best join method for the rewritten version of the query. This can occur if the IN list contains parameter markers or host variables which prevent the optimizer from using catalog statistics to determine the selectivity. This registry variable causes the optimizer to favor nested loop joins to join the list of values, using the table that contributes the IN list as the inner table in the join.

DB2_PARALLEL_IO

When reading data from, or writing data to table space containers, DB2 Universal Database^™ (DB2 UDB) may 7 use parallel I/O if the number of containers in the database is greater than 1. 7 However, there are situations when it would be beneficial to have parallel I/O enabled for single container table spaces. 7 For example, if the container is created on a single RAID device that is composed of more than one physical disk, you may 7 want to issue parallel read and write calls.

To force parallel I/O for a table space that has a single container, you can use the DB2_PARALLEL_IO registry variable. This variable can be set to "*" (asterisk), meaning every table space, or it can be set to a list of table space IDs separated by commas. For example:

`db2set DB2_PARALLEL_IO=* \{turn parallel I/O on for all table spaces}

``db2set DB2_PARALLEL_IO=1,2,4,8 \{turn parallel I/O on for table spaces 1, 2, 4, and 8}

`After setting the registry variable, DB2 UDB must be stopped (db2stop), and then restarted (db2start), for the changes to take effect.

DB2_PARALLEL_IO also affects table spaces with more than one container defined. If you do not set the registry variable, the I/O parallelism is equal to the number of containers in the table space. If you set the registry variable, the I/O parallelism is equal to the result of prefetch size divided by extent size. You might want to set the registry variable if the individual containers in the table space are striped across multiple physical disks.

For example, a table space has two containers and the prefetch size is four times the extent size. If the registry variable is not set, a prefetch request for this table space will be broken into two requests (each request will be for two extents). Provided that the prefetchers are available to do work, two prefetchers can be working on these requests in parallel. In the case where the registry variable is set, a prefetch request for this table space will be broken into four requests (one extent per request) with a possibility of four prefetchers servicing the requests in parallel.

In this example, if each of the two containers had a single disk dedicated to it, setting the registry variable for this table space might result in contention on those disks since two prefetchers will be accessing each of the two disks at once. However, if each of the two containers was striped across multiple disks, setting the registry variable would potentially allow access to four different disks at once.

The DB2 Configs menu allows two options, both of which run the DB2 Configuration Utility (useful when first installing TruckMate). To use these two options, the currently logged in User must be a 'db2admin' User. If the current User is not part of the 'db2admin' User group, then a prompt will appear asking for 'db2admin' User login credentials. Running the 'Install a Release / Service Pack Patch' option (Tools Menu) will run the two following options automatically.

Selecting this option opens the DB2 Configs that Affect TruckMate Performance window, which allows you to configure which Database Tables (on the currently connected Database) will be affected by the DB2CFG.BAT process.

In the DB2 Configs screen, rows that have values that are different than recommended will be highlighted.

If the current config value is lower than the recommended value, it will be highlighted in red and the checkbox to change it will be checked by default (it is highly recommended that you change the config). If the current config value is higher than the recommended value, this is not necessarily a bad thing (you may have had a good reason to tune the config to a higher value); the row will be highlighted in yellow (so that you review/notice it), but it will not be checked by default.

DB2 Bufferpools

On the DB2 Configs window, for any database other than the ISC4, all of the database’s 'bufferpools' are now listed below the last config (currently 'STMTHEAP'). For each bufferpool, the current and recommended minimum size are shown. A bufferpool’s size can be changed just like changing a config (enter desired value into 'To' column and check left-most column).

Just like configs, a bufferpool row will be highlighted in red or yellow if it is below or above the recommended minimum size (if set to Auto, the row is neither highlighted nor auto-recommended for a change).

Also, upon selecting the 'Advise' menu item, a popup message will indicate the number of settings that should be changed (i.e. status bar info) and indicate what the red/yellow highlighting mean.

Selecting this option runs the Database configuration utility (DB2CFG.BAT) on the ISC4 (security) Database. The DB2 Configs that Affect TruckMate Performance window will appear (as with the above option) with the same functionality.

Before DB Upgrade / After DB Upgrade

These two options are provided for making database upgrades a simpler process. The two options are very similar and use the same form. In both the 'Before Upgrade' and 'After Upgrade' windows, there are the following checkbox options:

Inoperative Objects (fix)

When you select this option, the SQL Execute program will scan your Database for any Inoperative DB2 Objects (Procedures, Triggers and Views) and will generate an SQL Script that will fix these erroneous objects. A window will appear, showing you the number of objects needing fixing, and once you click 'OK', a script will be generated and displayed on the Scripts Tab. To complete the process, just click the 'Execute' button to run the script.

Temporary Tables (drop)

Selecting this option will "clean-up" (drop) temporary system tables created when certain DB2 tools are used (like 'explain', 'advise', 'monitor', etc). Dropping these Tables will free up disk space.

Revalidate Objects

Selecting this option will create script to validate Data base objects. A decision window will popup asking if you want to run the script or not. The script should capture all invalid database objects.

Views (recreate)

Views can become inoperative:

The following steps can help you recover an inoperative view:

All of the Above

When you select this option you will be given the option to automatically execute the script that is generated. Select 'Yes' if you would like to automatically run the script, or 'No' if you would like to look it over prior to execution. Once this option is selected, a DB2 Cleanup / Repairs script will be generated that includes all the above processes.

Selecting this item will open a pop-up form that allows you to configure how the SQL Execute program functions behave. The SQL Execute Options form offers the following tabs:

Confirm Before Clearing

The clear function is used to erase whatever is currently being shown in one of 3 areas of the program. Setting this option will cause the program to prompt the User with a confirmation message box before it actually clears the contents of each or all of the the SQL tab, SQL Script Results Log, or the Script tab itself.

Open File on Saving

These settings allow you to configure the program’s behavior when a User selects the 'File Menu > Save Script to File' option. The three sections here: 'SQL', 'Script' and 'S.Proc' apply to the use of the 'Save Script to File' option when a User is on the SQL Tab, Script Tab or Stored Procedure Tab, respectively.

Show Toolbar

The tool bar that appears just below the menus in the main screen is configured to be displayed or hidden by the program by checking or un-checking this checkbox.

Show Status Bar

The program status bar which appears in the SQL tab and the Scripts tab can hidden or displayed by the program depending on whether this checkbox is checked or un-checked.

Restore Defaults

Clicking this button resets all the options on all of the tabs back to the program default.

The options on this tab control how the program behaves when the SQL tab is active.

Show record count

This toggles the record count function on and off. With the option turned on, when an SQL statement is executed, the number of records affected is displayed in the program status bar.

Display text results in list format

When this option is turned on, the records returned as the query results are displayed in the text tab one record at a time with the fields in a vertical list instead of as columns extending to the right as normal.

Include Query in text results

If this flag is checked, the query entered on the SQL Tab will be included at the top of the Text Tab when the query is executed.

Include column titles in text results

If this flag is checked, the column titles of the table will be included next to their values on the Text Tab when a query is executed.

Include column titles in csv results

If this flag is checked, the column titles of the table will be included with their values in the .CSV file that is created upon execution of a query.

Tab-delimit text results (instead of blank spaces)

If the 'Display text results in list format' option in not checked, and this flag is checked, the records will be displayed similar to a spreadsheet format on the Text Tab, and will be separated by a 'tab' (more space then a single 'space').

Format Inserts Tab for Readability

If instead you want to see only 1 line of text per INSERT statement when working with the SQL Tab > Inserts Sub-Tab, enable this flag.

Pad String CSV Fields (with Spaces) to Full Fixed Width

If you wish to open a file that was generated from the results of the SQL Tab in .CSV format with a text editor, you may wish to have text fields padded with spaces ('Pad Left'), just like fixed text format, so the columns line up and the data is easier to read. Or, you may wish to have text fields right-aligned ('Pad Right')when viewing the file in a spreadsheet program like Excel. Note that numbers and dates are not affected by this option.

Maximum history entries

This value determines how many SQL commands will be automatically entered into the SQL history log. Once the log is full, logging function will drop the oldest entry to allow the newest entry to be stored.

Text tab memo fields display width

When an SQL query returns a record with a BLOB (or CLOB) memo field in it that stores a text value, the text tab uses this value to determine the maximum number of characters from the text string that it will display. Any values from the text string past the limit are not displayed.

The options on this tab control how SQL Execute processes SQL scripts in the Script Files tab.

Note: Most SQL scripts written for TM50 and earlier use InterBase SQL dialect. This version of SQL uses the semi-colon as the statement terminator and a blank line to indicate the end of a procedure or function definition. If you are going to execute one of these scripts, you must set the statement terminator option to the semi-colon and turn on the Blank-line terminator option.

Many SQL scripts written after TM50 were written using DB2 SQL dialect, which uses the @ symbol as the statement terminator. As of TM60, all SQL scripts written for InterBase and DB2 use the @ symbol as the statement terminator. In these cases, the Statement Terminator should be set to @ and the Blank-line terminator option turned off.

Statement Terminator

This option refers to SQL scripts and the special character used to indicate the end of each individual command. The default setting is the @ symbol. See discussion below.

Working Directory

A directory path should be entered here. This path is used for three purposes: (a) When the Script Files tab is active, the first time the 'Load from File' function is used, it will open the file search window to this directory. (b) If a file name is referenced in the Script Files tab as part of the script to be executed

Show Progress

This option enables and disables the progress indicator. This is a pop-up window which appears while scripts are being processed by the program and shows what script file (if there is more than one) is being processed, what type of commands were processed, and if the commands were successful or generated errors or warnings.

Fix Quotes

When this option is turned on, the script processor will convert all single quotes in the script text to double single quotes before processing the script. This option should be set if running scripts on InterBase databases.

Abort on 1st Failure

When this option is checked, the SQL Execute program will abort any Script operations when the first failure occurs. This option is helpful for quickly identifying any problems in an SQL Script.

Ignore Drop Statements

Setting this option causes the script processor to skip any statements that drop a database object. This option should be turned off if the dependency checking function is turned on because it adds drop statements to scripts which should NOT be ignored.

Blank-line terminators

This option indicates if the program should treat blank lines as statement terminators or not. This option is available to allow older, InterBase sytle patches to be processed.

Abort on 1st Warning

When this option is checked, the SQL Execute program will abort any Script operations when the first warning occurs. This option is helpful for quickly identifying any problems in an SQL Script.

Load files to screen as

The options here control how scripts are displayed in the script contents area. If the option is set to Scripts, the text that makes up the script is loaded into the window for inspection. If the option is set to Files, only the script filename is written to the screen.

Note: This option also configures the program to process the contents of the scripts area as either scripts or files. This is no longer the case. SQL Execute can now process scripts that are made up of both SQL commands (whether manually keyed in or loaded from a script file) and file names (either just a file name or a fully qualified path and filename).

Alpha-sort files loaded via drag-and-drop

This option, when flagged (default), will make the SQL Execute program automatically sort files that are "dragged and dropped" into the program window alphabetically by filename. When not checked (disabled), then files are sorted as follows: selected file is always first, then the remaining files follow it from top to bottom.

The options on this tab control how the program logs the results of scripts that are processed.

The options on this tab control how the program signals to the User that the (delayed) processing of an SQL command or script is complete.

Visually notify (popup) on successful execution

You can choose to have the program display a message box indicating when one of these processes is complete.

Audibly notify (beep) on execution completion

You can choose to have the program play a sound (the wave file associated with the Windows beep event is used) when one of these processes is complete.

Only alert if goes over

This is a whole number value (no fractional minutes allowed) indicating how many minutes must have elapsed before the alert functions should be used.

That is, if one of the processes listed above is completed in less time than the number of minutes indicated, the alert will not appear.

If the value is set to 0 and all options are selected above, the alert will always appear. A value of 5 minutes or more is the recommended setting.

This Tab refers to the Dependency Checker function used in the Script Files Tab, before a patch is actually run on the database.

Dependencies occur when the definition of one database object references another database object. This means that for object B to exist, object A must exist first.

Example: procedure 'PR_VOID_CHK' as part of its definition uses procedure 'INSERT_CHECK_MSTR_EXP' to fill in the expense information into the void check record. 'PR_VOID_CHK' now cannot exist in the database unless 'INSERT_CHECK_MSTR_EXP' exists. It is dependant on the other procedure being defined first.

Scripts that drop and recreate procedures that other procedures depend on will cause problems if they fail to be processed successfully because the procedure depended on will no longer exist in the database.

The dependency checker function inspects scripts before they are to be processed to see if the procedures and functions which are to be defined are depended on by other procedures.

If they are, the depending procedures are dropped and recreated before and after the script by the Dependency Checker function in the correct order.

Note: The Dependency Checker can be fooled by scripts which do not themselves take dependencies into account.

Example: procedure 'C' depends on function 'B', which in turn depends on function 'A'. A script that redefines function 'A' and procedure 'C' must also redefine function 'B' even though its definition does not change.

Note: DB2 scripts are currently only inspected for function dependencies. Database administrators should also be aware that the 'PROCODR.SQL' file must exist in the TruckMate2013 folder. This is a special SQL script that defines the dependency hierarchy. Because the hierarchy is subject to change, an update to this script may be released and will need to be applied for the Dependency Checker to able to properly manage dependencies.

Always run automatically

This option should be set by default, although the dependency checker function can be run manually from the Tools menu.

Run in silent mode (in background)

When the dependency checker has completed inspecting the script(s) loaded into the Script Files window it reports to the screen what objects will be redefined and in what order. This option turns this reporting off.

Keep logic in known custom procedures & functions

If this option is flagged, then the body of any custom procedures and/or functions in a patch will be over-written with whatever the current content of that custom procedure/function is in the TruckMate database. This allows a measure of safety in the case that a patch contains a custom procedure or function that you do not want applied to your database (as you already have custom logic in that procedure/function).

Note: If you hand-type procedure or function changes with this option enabled- rather than working in an editor and copying the content into the Scripts Tab- then you will lose your changes, as this check will not be applied.

Re-create dep. view when a table is altered

If this option is flagged and an SQL script contains an 'ALTER TABLE' statement for a TruckMate Table on which a View is based, the SQL script will be formatted in a specific way. Whether a DROP/CREATE VIEW is added due to this option or due to the new view on view dependency check, the DROP statement will get added to the top of the script and the CREATE statement will get added to bottom (just like with Functions, Procedures, and Triggers).

Furthermore, Views will be dropped after other objects are dropped, and will be re-created before other objects are re-created.

Add drop/create statements to patch as

This option works in much the same way as the 'load files' option in the Scripts Tab. The dependency checker can insert the drop/recreate dependent objects statements directly into the Script Files window as statements, or it can write them into scripts files which are then included before and after the scripts to be run.

The options on this tab control how the program’s text editing functions behave in the SQL tab and the Script Files tab.

Show Line Numbers

Toggle line numbering on or off on the SQL tab and/or the Script Files tab

Colors/Highlighting

Toggle syntax highlighting of key words in the text on the SQL tab and/or the Script Files tab.

Auto Indent

Toggle auto-indenting of SQL commands and statements in the SQL tab and/or the Script Files tab.

Group Undo

Toggle the undo typing ('<Ctrl><Z>') for single keystrokes or groups of similar keystrokes in the SQL tab and/or the Script Files tab.

Show Special Char’s

Toggle the display of non-printing characters (carriage returns, etc.) in the SQL tab and/or the Scripts tab. This is useful if a script is pasted into the program from source that may use hidden text tags (such as the rich-text tags found in email).

Customize

Clicking the 'Customize' button opens the Colors/Highlighting Setup window, where colours and text display options can be customized. Click the 'Defaults' button to restore the TruckMate default color scheme for SQL Execute.

The 'Sample' window will display what your changes will look like when applied to the SQL Scripts windows.

Word-Wrap

Users can turn on Word-Wrap functionality for each of the 3 sections of the SQL Execute program by checking their respective flag. When Word-Wrap is enabled, if a line is too wide to fit across the width of the form, then instead of a horizontal scrollbar, the remainder of the line is displayed on a new line. Note that this is for display purposes only; the text is still stored as a single line. In fact, if you have the 'Show Line Numbers' option enabled, you will see that it does not change the numbering; rather, a special character is displayed (in the left gutter) for the wrapped line.

Right Margin

Users can set the Right Margin (vertical line) value for each of the 3 sections of the SQL Execute program by toggling these three numerical values. Users can move the right-margin to any column position they want (between 1 and 1024), or even turn it "off" (by setting the position to '0'). Note that this is just a guideline indicator, kind of like the bell when you get close to the end of the line on a type-writer (i.e. you can still type beyond the margin). However, to make scripts or SQL statements readable (without having to turn on word-wrap), developers are encouraged to keep line widths under a certain max- having the right-margin reminds you when your line is too long and you should start a new one. Again, users who only want to run statements and scripts (and never write them themselves), will now have the option to "hide" (get rid of) this line (by setting the new option to 0).

Note: This Tab will only be visible if you are currently connected to a Multi-Schema Database that is not configured as a 'Stand-Alone' Database Type.

Always Run Automatically

When this option is checked, whenever an SQL Script is executed, be it a simple User-entered script or something as large as an Upgrade Patch, the SQL Execute program will automatically call the Multi-Schema Script Processor (MSSP) Utility, which will configure the script and apply it to all connected Schemas.

Run in Silent Mode (in background)

If you check the option 'Run in silent mode (in background)', then the Multi-Schema Script Processor (MSSP) will go ahead and modify the script. Note that if the patch contains any 'CREATE TABLE' statements, these will be replicated in all the non-'Stand-Alone' company Schemas, NOT in the 'TMCOMMON' Schema.

If this option not checked, and the script does not contain a 'CREATE TABLE' statement, you will simply be asked 'Yes/No' to process the script. If silent option not checked, and script does contain 'CREATE TABLE' statements, you will be shown a grid with a row for every table to be created and a column for every non-stand-alone schema. You may check/uncheck each combination and click 'OK' or click 'Cancel' to abort. If not aborted, the processor will then attempt to re-write the patch/script in the Script Tab per the Schema structure of your database.

Warn when non schema owner run scripts

Most schema change commands (ALTER, DROP, etc) are in patches that are run from the Scripts Tab and when you do so, if you are not logged in as the schema owner (usually LYNX), you will get a popup warning.

However, it is possible to run a schema change statement (albeit one at a time) from the sql tab. Either disable this option (only allow SELECT’s from the sql tab), or at the very least, warn user that they are not the schema owner.

Auto-check for inoperative objects before closing app or switching db’s

If this option is checked, and you are logged in as the schema owner, then under ceratin conditions, the SQL Execute will automatically check to see if there are any inoperative objects. This check will occur when:

If there are inoperative objects, you will be asked whether you wish to fix them now. If you click 'Yes', the Fix Inoperative Objects script will load and auto-execute, and your action that caused the check (leaving the database, for example) will be aborted.

Unlike the automatic Security Patcher check, you can click 'No' to continue disconnecting (and run the 'Fix Inoperative Objects' option another time).

The Release Patch Tab contains eight separate checkbox options that allows the administrator to customize how much interaction he/she wants during release patch upgrades.

For example, if 'Skip "Are you Sure?" (last change to abort)' is flagged, the user will not be prompted to confirm if they want to run the release patch once the process has begun.

Selecting this item opens the Database Security Patcher Wizard.

There are two separate types of security enforced in the TruckMate system: (1) program security, and (2) database security. Program security is used to control what programs and parts of programs are accessible to different Users. Database security is used to control what parts of the database are accessible to different Users.

Example: A User may be given program access to the Customer Service program, but they are not given access to the table 'ACHARGE_TLORDER', which stores the accessorial charges associated with Freight Bills. If this User attempts to open Customer Service, they will encounter a database error because Customer Service cannot open without access to accessorial charges.

The security patcher Wizard should be used to re-apply database security to the database whenever you:

The Wizard will walk you through three steps: you must first provide the password for the owner of the database, you then specify what to include in the patch log, and then security patch process begins. When the patching is completed, you can choose to review the patch log or close the patcher Wizard.

If you select the review the patch log, you will be prompted to enter a filename and directory location where the patch log will be saved to before it opens.

Notes:

Selecting this option opens the DB2 Script Generation window, which allows you to configure what information will be applied to the DB2 Script that will be generated:

Source Directory

Click the search button to browse to the folder which contains your main TruckMate '.SQL' Source Files.

Source Files

Check the box in the 'Load' column associated with the '.SQL' Source Files you wish to use when generating the Batch File. '.SQL' statements will be gathered from those files that are checked only. You can use the 'All' or 'None' buttons to quickly select/clear all the options here.

Patch Files

If you wish to use previously created SQL 'Patch Files' ('.SQL' formatted files), check this option and use the 'Add' button to browse your system for the '.SQL' files you would like to include in the Script Generation.

Only Include Files >=

This option allows you to quickly filter those files that are 'Greater than or Equal To'- or 'newer than'- a specified date. Use this option if you only want to apply the most recent Patches to your database. You may select a date by clicking the pull down menu arrow and picking a date from the Calendar menu that will display.

Re-Load VIEW_ORDER Table

When this flag is checked, the VIEW_ORDER Table will be created by the schema object creation script that this window builds.

Note: The 'VIEWORDR.SQL' file must exist in the default TruckMate install directory for this functionality to work correctly.

Re-Load Tables

Create Script

Selecting the 'OK' button will generate the DB2 Script based on your selected options in this window.

The Table Move Wizard takes you through the steps to increase the TableSpace size of your database tables. Certain database tables ('TLORDER', 'PTLORDER' and 'EDI_PROFILES' to name a few) require an '8k' TableSpace size. If your database tables reside in '4k' size, you must complete this Wizard in order to move your tables into the proper memory size allocations. You also have the option to move a table to another Schema.

The Table Movement selection process has been streamlined, and now allows you to select multiple tables at once.

On the first page of the wizard you must use the drop-down menu to select the 'from' tablespace (where the Tables will be moved from). It defaults to 'All' (to get the full Table list). However, you can select a specific tablespace to shorten the tablelist on the next wizard page. This is especially useful when moving tables from a MANAGED BY SYSTEM tablespace to a MANAGED BY DATABASE tablespace (select all tables from a particular tablespace and move them to the corresponding DMS_ tablespace).

On the second page of the Wizard you will see all of the TruckMate system tables- simply check the tables that you’d like to be moved to the larger TableSpace.

Before inserting data, the program will check for 'duplicates' from the source table (where a 'duplicate' is a row with the same primary key value(s) as a row in destination table). If any duplicates are found, SQL Execute will prompt the User with this option:

Tables that require the 8K TableSpace are:

Once you have selected a Table (or Tables) to be moved to a new TableSpace, you will see the screen below. Select 'Next' to complete the process once you have configured the required options here.

TableSpace

Use the drop-down menu to select the new TableSpace you would like to move the Table(s) to.

Selecting this option opens the Database Compare Wizard, which allows you to compare two Database schemas.