Tpump now called stream (TD12 - Teradata implementation for real time trickle feed) under the covers of TPT; it’s a row level operation which based on the hashing algorithm does the DML operation on the hash row. There could be an application level contention based on the NUPI defined on the table having operations on the same rowhash ID. This can be controlled by proper configuration of the parameters and settings (SLEEP, TENACITY, SERIALIZATION, SESSIONS etc). The session blocking is only momentary but sometimes this can lead to overhead contention and nuisance alerts to the DBAs. Having more sessions is as harmful for performance as having as little as one session, so careful analysis in deciding the sessions will not only improve TPUMP throughput but also improve overall system performance.
System level design for TPUMP operation w/ table designed as fallback
When a DML operation is performed on a fallback table P2P (point-to-point) protocol message is spawned from the main primary AMP to the fallback AMP. The fallback AMP now has the copy as a fallback. This entire operation needs two AWTs (Amp Worker Task) doubling the number as opposed to regular non fallback DML operation.
For e.g.: How it works when a user submits an insert operation on table with fallback definition
Steps:
1. An Amp Worker Task is assigned on the AMP that has the primary copy. Once the insert is successful a message is transmitted to the fallback AMP.
2. Once the fallback AMP receives the message it will try to insert into its AMP so needed another AWT, the AWT on the primary is not relinquished till the operation is successful on the fallback. So two AWTs are on hold until the fallback operation is successful. If the fallback AMP doesn’t get the AWT right away it will be in the queue and the primary AMP will not relinquish its AWT. You can call it as a design flaw or data integrity but that is how it works.
So if you are seeing any delays on the top of deadlocks that TPUMP has on because of multiple sessions, it also could be AWTs which are so minimal but on a busy day with a flow control situation, it worsens to the whole performance package.
Recommended actions/solutions:
1. I don’t recommend this but some sites do this; for Tpump Jobs you can specify some reserved AWTs. Because reserving an AWT will have a 2n+1 rule in place, where n is the number of AWTs to be reserved. The spawning of AWTs make it worse when we reserve.
2. Have both object and workload throttles to have so many AWTs available in the spool so that TPUMP jobs have good throughput. I recommend this approach and it worked much better.
Tuesday, August 31, 2010
Canceling a rollback Job
A big Rollback Job can bring the system to its knees. If your DBS setting parameter RollbackPriority is set to FALSE then it takes the priority and will not honor any rules related to CPU assignment or TASM or anything. The Rollback job runs in RUSH priority and will bring down your system. This and the ALTER statement will never honor your CPU limitation.
From TD12 I've started canceling of Rollback Job but it is made possible from V2R5.1 release. This may lead even to drop the table on which the cancel rollback is applied, so chances are to lose the data. if you've it from the back up you either can restore/copy the data from the backups.
so what causes a bad rollback?
A user submitting a DML operation which after running a while and not giving the results in the stipulated time or is taking too much time, either the user wants to abort it or wait till it finishes, if he choose to abort and this will cause a rollback on the table which takes more time to relinquish the resources.
How to avoid it?
Stop issuing the rollback or if you issued a rollback then try to cancel the rollback. I suggest in the DBS Control Record of General Fields for the column RollbackPriority set to TRUE which will nullify the rollback to be a resource hog.
For some small rollback jobs the time taken could be negligible but for some big jobs the rollback needs to finish asap so that the ETL team can load further or start loading to the same table with correct update/insert statements.
Notes:
Remember that you may want to drop the table and create a new one most of the time when you cancel a rollback job as table state could be corrupted for most of the times.
The rcvmanager utility is used to do the Job using the root password for the system assuming it is a LINUX box.
Steps to cancel a Rollback Job:
1) Log into the node using the root username and password.
2) Connect to the CNS by typing CNSTERM 6 to find where rcvmanager is? usually it is
in window 1 and press control C to come out of it and type cnsterm 1
3) type start rcvmanager and once you get the prompt for enter the command type -
list rollback tables
4) once you get the list of the rollback tables and get the tableID for the table
you need to cancel rollback.
5) your table ID is in hex vaule so makes sure you copy values before and after the
colon (:)
6) Issue the cancel rollback statement; cancel rollback on table table ID and it
prompts for the DBC password.
7) To verify the status you can issue - list cancel rollback tables to get the
Information about the rollback status, it will be in pending state.
8) Once done you can see the system performance and its speed. At times during a
rollback I observed no more logons are allowed by the database; once the rollback
cancellation all the jobs and resources come to normal behavior.
Hope this article helps to folks who fall in this situation.
From TD12 I've started canceling of Rollback Job but it is made possible from V2R5.1 release. This may lead even to drop the table on which the cancel rollback is applied, so chances are to lose the data. if you've it from the back up you either can restore/copy the data from the backups.
so what causes a bad rollback?
A user submitting a DML operation which after running a while and not giving the results in the stipulated time or is taking too much time, either the user wants to abort it or wait till it finishes, if he choose to abort and this will cause a rollback on the table which takes more time to relinquish the resources.
How to avoid it?
Stop issuing the rollback or if you issued a rollback then try to cancel the rollback. I suggest in the DBS Control Record of General Fields for the column RollbackPriority set to TRUE which will nullify the rollback to be a resource hog.
For some small rollback jobs the time taken could be negligible but for some big jobs the rollback needs to finish asap so that the ETL team can load further or start loading to the same table with correct update/insert statements.
Notes:
Remember that you may want to drop the table and create a new one most of the time when you cancel a rollback job as table state could be corrupted for most of the times.
The rcvmanager utility is used to do the Job using the root password for the system assuming it is a LINUX box.
Steps to cancel a Rollback Job:
1) Log into the node using the root username and password.
2) Connect to the CNS by typing CNSTERM 6 to find where rcvmanager is? usually it is
in window 1 and press control C to come out of it and type cnsterm 1
3) type start rcvmanager and once you get the prompt for enter the command type -
list rollback tables
4) once you get the list of the rollback tables and get the tableID for the table
you need to cancel rollback.
5) your table ID is in hex vaule so makes sure you copy values before and after the
colon (:)
6) Issue the cancel rollback statement; cancel rollback on table table ID and it
prompts for the DBC password.
7) To verify the status you can issue - list cancel rollback tables to get the
Information about the rollback status, it will be in pending state.
8) Once done you can see the system performance and its speed. At times during a
rollback I observed no more logons are allowed by the database; once the rollback
cancellation all the jobs and resources come to normal behavior.
Hope this article helps to folks who fall in this situation.
Why do we need Query Banding?
By enabling Database Query Log (DBQL) and installed the performance data collection and reporting (PDCR) database in all Teradata environments the organization will have plethora of log information, statistical information and other audit information.
The DBQL and PDCR log and store historical data about queries including query duration, CPU consumption and other performance metrics. The data offers information to calculate suspect query indicators such as large-table scans, skewing and highly consumptive joins. In addition, the data provides diagnostic metrics to help tune applications, monitor performance, manage capacity and operate the Teradata system at peak efficiency.
To fully leverage DBQL and PDCR capabilities, it is imperative for the BI group to enable query banding in universe, which will provide us a valuable tool to band each BusinessObject query submitting to Teradata with a number of attributes and values that provide detailed information about the query. The information allows us to effectively tune BusinessObjetcs applications by identifying and tuning target reports and proactively preventing report performance problems.
Query banding is especially valuable for our BusinessObjects applications since we use the generic accounts to access Teradata database. Query banding will enable us to identify the unique attributes of each request.
How to set Query Banding in universe parameter?
Teeadata database supports passing of parameters before the SELECT statement. The BEGIN_SQL parameter allows us to prefix SQL statements with the query banding values each time a SQL statement is generated. Below are steps to set up the query banding in universe:
• Start the Universe Designer
• Open your Universe (for existing Universes, please import them first)
• Click on File menu -> Parameters…
• Click on Parameter tab
• In the "Parameter" list, click on BEGIN_SQL parameter
If does not exist, add one.
• Enter value for BEGIN_SQL:
Copy following name/value pairs into the Value box:
SET QUERY_BAND = 'USER = '@Variable('BOUSER')'; DOCNAME = '@Variable('DOCNAME')'; DPNAME = '@Variable('DPNAME')'; DPTYPE = '@Variable('DPTYPE')'; UNIVERSE = '@Variable('UNVNAME')'; UNIVERSEID = '@Variable('UNVID')';' FOR TRANSACTION;
Variable description:
a) BOUSER - user login
b) DOCNAME - the name of the document
c) DPNAME - the name of the Data
d) DPTYPE - the type of the Data
e) UNVNAME - the name of the universe
f) UNVID - the ID of the universe used
• Click Replace
• Click OK
• Save the Universe
• Export the Universe to CMS
The DBQL and PDCR log and store historical data about queries including query duration, CPU consumption and other performance metrics. The data offers information to calculate suspect query indicators such as large-table scans, skewing and highly consumptive joins. In addition, the data provides diagnostic metrics to help tune applications, monitor performance, manage capacity and operate the Teradata system at peak efficiency.
To fully leverage DBQL and PDCR capabilities, it is imperative for the BI group to enable query banding in universe, which will provide us a valuable tool to band each BusinessObject query submitting to Teradata with a number of attributes and values that provide detailed information about the query. The information allows us to effectively tune BusinessObjetcs applications by identifying and tuning target reports and proactively preventing report performance problems.
Query banding is especially valuable for our BusinessObjects applications since we use the generic accounts to access Teradata database. Query banding will enable us to identify the unique attributes of each request.
How to set Query Banding in universe parameter?
Teeadata database supports passing of parameters before the SELECT statement. The BEGIN_SQL parameter allows us to prefix SQL statements with the query banding values each time a SQL statement is generated. Below are steps to set up the query banding in universe:
• Start the Universe Designer
• Open your Universe (for existing Universes, please import them first)
• Click on File menu -> Parameters…
• Click on Parameter tab
• In the "Parameter" list, click on BEGIN_SQL parameter
If does not exist, add one.
• Enter value for BEGIN_SQL:
Copy following name/value pairs into the Value box:
SET QUERY_BAND = 'USER = '@Variable('BOUSER')'; DOCNAME = '@Variable('DOCNAME')'; DPNAME = '@Variable('DPNAME')'; DPTYPE = '@Variable('DPTYPE')'; UNIVERSE = '@Variable('UNVNAME')'; UNIVERSEID = '@Variable('UNVID')';' FOR TRANSACTION;
Variable description:
a) BOUSER - user login
b) DOCNAME - the name of the document
c) DPNAME - the name of the Data
d) DPTYPE - the type of the Data
e) UNVNAME - the name of the universe
f) UNVID - the ID of the universe used
• Click Replace
• Click OK
• Save the Universe
• Export the Universe to CMS
Oracle to Teradata Migration Project tips
Introduction:
The decision to move to Teradata from an OLTP systems like oracle etc, with multiple data marts into an Enterprise Data warehouse is not complicate to start with but this needs right skills and planning to do and this gives the edge for the business to have the 3NF physical model, linear scalability, mixed workload capacity in an active and challenging business day-to-day life.
Birds Eye view agenda:
• Get the business requirements from the client anything that is necessary/related to the migration
• Have an approach how to design the physical database like tables, Indexes, Views and other database objects.
• All the business related Log and Historic Information related to data migration.
• Incremental Load conversion
Detail Technical and Business artifact for Migration Implementation
Client related Infrastructure:
• Business related rules and triggers embedded in data
• Data model of the existing business and the model for the tales that needs to be moved.
• All the software and hardware compatible with the Release of the Teradata that is in place.
• Make sure that the requirements are at least frozen during the port operations at the sending side (Oracle/DB2).
• If multiple data marts are ported do one at a time and then determine name of the production database and view database.
• Design a strategy in creating profiles and users.
• As part of the data movement and migration make sure we have the information of what gets moved and how, meaning are we landing onto a disk and doing or direct fork lift?
• Determine the right PI in table designing talking to the ETL and Front end Reporting folks.
• Design initial load jobs with the assumption that the target tables are created.
• Determine the type of slowly changing dimensions and design accordingly.
Approach for Physical DB design and conversion:
• Teradata maximum length of a database object is 30, so keep in mind always about the naming convention, like DB2 OS390 Maximum name length = 18 DB2 Unix Maximum name length = 128, no problem with Oracle as its limit is also 30 characters.
• Ask if the client has any naming standard, if so we can follow it; so that a mapping sheet is created for 120 to 30 characters and have the business sit with us to review the sheet and agree upon for a valid names
• Like have all the DDL and indexes to be converted, for DB2
o Use the DB2 system Catalog as input into a conversion script
o Use data modeling tool like ERWIN and do the reverse engineering
o Have a DB2LOOK command and an editor.
o Same thing goes with the Indexes, go with Teradata index creation criteria; use the columns and index table to u unload from the DB2
o Enough testing needs for converting UDFs into Teradata, either the source is DB2 or any other database because the language and features are different. The easy way to handle this is to put the UDF is to create macros or stored procedure and UDTs to data containers in stored procedure.
Log and Historic migration:
• Determine the migration approach, is it going to be an ETL tool doing the pull and push or the source data is put into flat files and then use this to load into Teradata using load utilities; since the tables are empty a FASTLOAD can be used to do the loading.
• The SLA violation is something that determines how to load the data. From legacy systems to a system using legacy ETL and then the Teradata ETL methodology to production or use a scripting tool to generate the load and unload scripts to get the data in place, the second one is an automated process which loads and unloads at the same time (using NDM technology)
• Have all the rules next to you like for instance the flat file format rules for transfer to Teradata, what if it is delimited to fixed or both.
Incremental Load conversion:
• Requirement can be completely ported to Teradata and run the loads or during the port operations the data could be loaded to the DB2/Oracle database; there are ways to handle this:
o Either stamp with a date and the migration moves the data from a point in time also called Incremental load synchronization
o The other way is a refresh, the data is moved at the very end of the migration process after the source system finishes it last run before the first run of the new system, if this is not possible for business continuity reason then a custom solution needs to be implemented to capture the change.
Process flow for a smooth data migration
• The design should be in place for the migration before testing anything with all validation in place
• Then starts the ETL and the specs to build followed by migration of data
• The Integration testing comes into picture with all the sync effort with the parallel systems if it is implemented that way.
• For huge migration projects automation is recommended like create process, macros in place for real time reporting status for tracking purpose.
Conclusion:
For any projects planning phase is so important. Measure thousand times before you cut. I took some time to write few things to share my knowledge and experience as an Lead Teradata DBA, DB Architect and GoldenGate Specialist with strong ETL Architectural development, I put this document as a high level to share my knowledge. I’ll be more than happy if this document can help in any of the missing gaps of your planning and analysis phase.
The decision to move to Teradata from an OLTP systems like oracle etc, with multiple data marts into an Enterprise Data warehouse is not complicate to start with but this needs right skills and planning to do and this gives the edge for the business to have the 3NF physical model, linear scalability, mixed workload capacity in an active and challenging business day-to-day life.
Birds Eye view agenda:
• Get the business requirements from the client anything that is necessary/related to the migration
• Have an approach how to design the physical database like tables, Indexes, Views and other database objects.
• All the business related Log and Historic Information related to data migration.
• Incremental Load conversion
Detail Technical and Business artifact for Migration Implementation
Client related Infrastructure:
• Business related rules and triggers embedded in data
• Data model of the existing business and the model for the tales that needs to be moved.
• All the software and hardware compatible with the Release of the Teradata that is in place.
• Make sure that the requirements are at least frozen during the port operations at the sending side (Oracle/DB2).
• If multiple data marts are ported do one at a time and then determine name of the production database and view database.
• Design a strategy in creating profiles and users.
• As part of the data movement and migration make sure we have the information of what gets moved and how, meaning are we landing onto a disk and doing or direct fork lift?
• Determine the right PI in table designing talking to the ETL and Front end Reporting folks.
• Design initial load jobs with the assumption that the target tables are created.
• Determine the type of slowly changing dimensions and design accordingly.
Approach for Physical DB design and conversion:
• Teradata maximum length of a database object is 30, so keep in mind always about the naming convention, like DB2 OS390 Maximum name length = 18 DB2 Unix Maximum name length = 128, no problem with Oracle as its limit is also 30 characters.
• Ask if the client has any naming standard, if so we can follow it; so that a mapping sheet is created for 120 to 30 characters and have the business sit with us to review the sheet and agree upon for a valid names
• Like have all the DDL and indexes to be converted, for DB2
o Use the DB2 system Catalog as input into a conversion script
o Use data modeling tool like ERWIN and do the reverse engineering
o Have a DB2LOOK command and an editor.
o Same thing goes with the Indexes, go with Teradata index creation criteria; use the columns and index table to u unload from the DB2
o Enough testing needs for converting UDFs into Teradata, either the source is DB2 or any other database because the language and features are different. The easy way to handle this is to put the UDF is to create macros or stored procedure and UDTs to data containers in stored procedure.
Log and Historic migration:
• Determine the migration approach, is it going to be an ETL tool doing the pull and push or the source data is put into flat files and then use this to load into Teradata using load utilities; since the tables are empty a FASTLOAD can be used to do the loading.
• The SLA violation is something that determines how to load the data. From legacy systems to a system using legacy ETL and then the Teradata ETL methodology to production or use a scripting tool to generate the load and unload scripts to get the data in place, the second one is an automated process which loads and unloads at the same time (using NDM technology)
• Have all the rules next to you like for instance the flat file format rules for transfer to Teradata, what if it is delimited to fixed or both.
Incremental Load conversion:
• Requirement can be completely ported to Teradata and run the loads or during the port operations the data could be loaded to the DB2/Oracle database; there are ways to handle this:
o Either stamp with a date and the migration moves the data from a point in time also called Incremental load synchronization
o The other way is a refresh, the data is moved at the very end of the migration process after the source system finishes it last run before the first run of the new system, if this is not possible for business continuity reason then a custom solution needs to be implemented to capture the change.
Process flow for a smooth data migration
• The design should be in place for the migration before testing anything with all validation in place
• Then starts the ETL and the specs to build followed by migration of data
• The Integration testing comes into picture with all the sync effort with the parallel systems if it is implemented that way.
• For huge migration projects automation is recommended like create process, macros in place for real time reporting status for tracking purpose.
Conclusion:
For any projects planning phase is so important. Measure thousand times before you cut. I took some time to write few things to share my knowledge and experience as an Lead Teradata DBA, DB Architect and GoldenGate Specialist with strong ETL Architectural development, I put this document as a high level to share my knowledge. I’ll be more than happy if this document can help in any of the missing gaps of your planning and analysis phase.
Fast Path Insert and Delete Statements.
Rules for Fast Path Insert and Delete Statements
Solution/source: Knowledge article
Tech Note
Unconstrained (Fast Path) DELETE Processing
When submitted by itself as a transaction, an unconstrained delete
(DELETE...ALL) is processed very quickly.
An unconstrained DELETE is one where no conditions are attached, that is, all
rows are deleted. When an unconstrained delete is submitted as part of a
multi statement transaction, the speed with which it is processed depends on its
position in the transaction.
To enable fast path processing, which defers processing until a transaction ends
and allows rollback processing to be almost instantaneous, positioning must be
as follows, depending on the mode being used.
In Teradata Mode:
FOR this type of transaction Positioning must be as follows
Implicit (such as a multistatement macro or BTEQ request) DELETE...ALL must be the last statement in the request.
DELETE...ALL must be immediately followed by the END TRANSACTION
statement that terminates the currently open transaction.
Explicit This implementation is not available to a Teradata embedded SQL application
because the DELETE and END TRANSACTION statements must fall into the
same request packet.
======================================================================
Fast Path INSERT … SELECT Statements
Multistatement INSERT … SELECTs are optimized so that each statement
except for the last in the series returns a ‘zero row inserted’ message as a
response. The retrieved rows for each SELECT are sent to an empty target table.
The last INSERT … SELECT statement returns the total number of rows
inserted for the entire request and sorts and merges the spool table into the
target table.
Columns defined with the COMPRESS option cannot participate in fast path
optimizations, so if you perform an INSERT … SELECT operation on
compressed columns, fast path optimization is not specified by the Optimizer
when it creates an access plan.
Rules for Fast Path INSERT … SELECT Statements
There are several restrictions that must be observed in order for the high
performance fast path optimization to succeed in a multistatement request.
These rules are as follows:
• The target table must be empty.
• All INSERT statements in the multistatement request must have the same
target table.
• Only INSERT statements can be included in the request.
If you insert other statement types into the multistatement request, the fast path
optimization does not occur (only the first INSERT … SELECT in the series is
optimized) and performance degrades accordingly.
Solution/source: Knowledge article
Tech Note
Unconstrained (Fast Path) DELETE Processing
When submitted by itself as a transaction, an unconstrained delete
(DELETE...ALL) is processed very quickly.
An unconstrained DELETE is one where no conditions are attached, that is, all
rows are deleted. When an unconstrained delete is submitted as part of a
multi statement transaction, the speed with which it is processed depends on its
position in the transaction.
To enable fast path processing, which defers processing until a transaction ends
and allows rollback processing to be almost instantaneous, positioning must be
as follows, depending on the mode being used.
In Teradata Mode:
FOR this type of transaction Positioning must be as follows
Implicit (such as a multistatement macro or BTEQ request) DELETE...ALL must be the last statement in the request.
DELETE...ALL must be immediately followed by the END TRANSACTION
statement that terminates the currently open transaction.
Explicit This implementation is not available to a Teradata embedded SQL application
because the DELETE and END TRANSACTION statements must fall into the
same request packet.
======================================================================
Fast Path INSERT … SELECT Statements
Multistatement INSERT … SELECTs are optimized so that each statement
except for the last in the series returns a ‘zero row inserted’ message as a
response. The retrieved rows for each SELECT are sent to an empty target table.
The last INSERT … SELECT statement returns the total number of rows
inserted for the entire request and sorts and merges the spool table into the
target table.
Columns defined with the COMPRESS option cannot participate in fast path
optimizations, so if you perform an INSERT … SELECT operation on
compressed columns, fast path optimization is not specified by the Optimizer
when it creates an access plan.
Rules for Fast Path INSERT … SELECT Statements
There are several restrictions that must be observed in order for the high
performance fast path optimization to succeed in a multistatement request.
These rules are as follows:
• The target table must be empty.
• All INSERT statements in the multistatement request must have the same
target table.
• Only INSERT statements can be included in the request.
If you insert other statement types into the multistatement request, the fast path
optimization does not occur (only the first INSERT … SELECT in the series is
optimized) and performance degrades accordingly.
Subscribe to:
Posts (Atom)
