Number of load jobs running on the system

To get a count of load jobs that are currently executing, you can use the following SQL.

SELECT COUNT(DISTINCT LogonSequenceNo) AS Utility_Cnt
FROM DBC.SessionInfo
WHERE Partition IN ('Fastload', 'Export', 'MLoad');

DBS Control parameters (MaxLoadTasks and MaxLoadAWT) can be used to control the number of concurrent load utilities running in a system.

Note: If TASM utility throttles are used, then the utility throttles override the MaxLoadTasks and MaxLoadAWT values and uses 60% (of AWTs) as the setting for the AWTs.

RELEASE MLOAD Statement

Once MLOAD job or TPT Update operator execution has begun, table headers are updated in the target tables indicating that a MLOAD is in progress. if the MLOAD fails, target tables are still considered under the control of the MLOAD and access to them will be restricted accordingly.

The RELEASE MLOAD statement provides a way to return tables to general availability where there is no desire to restart the MLOAD. If the specified table is in the Preliminary, DDL or the early part of the Acquisition phase, the RELEASE MLOAD statement makes the table completely accessible and prevents any attempt to restart the MLOAD.

 If the MLOAD had proceeded into the Application phase, the RELEASE MLOAD statement is rejected and the job must be restarted or until the transaction with the lock completes or if the point of no return has occurred(i.e. DELETE statement is sent to the DBC)

To successfully complete a RELEASE MLOAD, the following procedure must be followed:

1. Make sure MLOAD is not running; abort it if it is. (Note: MLOAD is still in a re-startable state if aborted. If it is past the point of no return, go to step 4.)

2. Enter RELEASE MLOAD (try IN APPLY if in application phase with caustion)

3. If successful, drop the work and error tables.

  4. If not successful, determine if past point of no return. If so, either restart MLOAD and let it complete, or drop target, work, and error tables.

Example:

release MLOAD Inventory;

release MLOAD Order IN APPLY;

Types of spools in Teradata

Teradata Database draws spool space dynamically from unused system perm space, there are mainly three types of spool usage in the system.

Volatile Spool:
Volatile spool is used for volatile table creation and  is retained until the Transaction completes (unless the table was created with ON COMMIT PRESERVE ROW)
or Table is dropped manually during the session
or Session ends
or Teradata Database resets

Intermediate Spool:
Intermediate spool is used during query processing, the spool are retained until they are no longer needed by the query for which they were created. You can determine when intermediate spool is flushed by examining the output of an EXPLAIN.
Note: The first step performed after intermediate spool has been flushed is designated “Last Use.”
or Teradata Database resets

Output Spool:
Output spool is used mainly for returning the result rows while responding, these are either Response rows returned in the answer set for a query or during Rows updated within, inserted into, or deleted from a base table

Find table skew in teradata

To find skew factor for table in Teradata, we can use below query.

 SELECT 

TABLENAME,

SUM(CURRENTPERM) /(1024*1024) AS CURRENTPERM, 

(100 - (AVG(CURRENTPERM)/MAX(CURRENTPERM)*100)) AS SKEWFACTOR 

FROM 

DBC.TABLESIZE 

WHERE DATABASENAME= 'Mydatabase_name'

AND TABLENAME ='myTable_name'

GROUP BY 1;

Simply replace the "Mydatabse_name" and "myTable_name" and run the query.

Find "Bad character in format or data" using OTranslate function

Bad character in format or data

======================

This error is reported when you try to translate a (Varchar or char) column with special characters in it to (Decimal or integer).When a query fails with such errors, the primary challenge is to find our where the problems lies and which column might have special character. the answer is where there is explicit or implicit conversion happening from character columns to integer.

More cumbersome job is to find what is that value in column which is causing the issue, here is a technique using which one can identify the value quickly.

example:

  sel roll from perf_app.$ab_temp   WHERE CHAR_LENGTH(roll) <> CHAR_LENGTH(oTranslate(roll, '/_#.'',-*)(:"', ''))

here the sys_fnlib.oTranslate function can identify the special character and provide the lenth of the value after replacing with ''(blank).