Skip to content

Unclassified: Teradata User Defined Function (UDF) Automatic Recompilation Process SEC=UNCLASSIFIED

This document outlines the approach to implement a process that automatically updates an array based User Defined Function based on an insert into a database table. The documented example is for Timezones. The extent to which this process is followed depends on the change management requirements and the nature of the individual UDF. For example the process could end at the point at which the C program is created which is then Unit Test prior to Production deployment. The example was developed in a Teradata environment but should be applicable to other RDBMS’.

<<Timezone UDF Automated Recompilation v1.0.doc>>
Timezone UDF Automated Recompilation v1.0.doc

Unclassified: Perl Script to Count the Rows of a file SEC=UNCLASSIFIED

Copy the following to a text file, update the file_name with the name of the file that you want to count the rows of and save it as Row_Count.PL.

#!/bin/perl

open(INFILE, "<g:\File_Name.TXT") or die "Can’t open input.txt: $!";

open(OUTFILE, ">g:\File_Name_count.TXT") or die "Can’t open output.txt: $!";

$count = 0;

$line = <INFILE>;

while ($line ne "") {

$count = $count +1;

$line = <INFILE>; }

print OUTFILE "$count";

Unclassified: Perl Script to Pad length of data file SEC=UNCLASSIFIED

#!/bin/perl

open(INFILE, "<g:\Insert_Filename.TXT") or die "Can’t open input.txt: $!";

open(OUTFILE, ">g:\Insert_Filename2.TXT") or die "Can’t open output.txt: $!";

$line = <INFILE>;

while ($line ne "") {

$pad = <UPDATE with length 154 > – length ($line);

chomp $line;

$new_line = $line.(" "x$pad);

print OUTFILE "$new_linen";

$line = <INFILE>;

}

How to run the Teradata Reserved Word Script prior to an RDBMS upgrade

How to run the Teradata Reserved Word Script prior to an upgrade.

The purpose of the reserved word script is to identify any database objects that contain reserved words that may cause issues when the database is upgraded.

The reserved word script is provided by Teradata customer services engineers performing the upgrade and will be named something like : pre_upgrade_prep.pl

  1. Step 1 – Log onto the <SYSTEM> DEV ETL server, navigate to the directory in which the pre_upgrade_prep.pl file is located. Launch a command prompt.

2. Step 2 – For the <SYSTEM> DEV system enter the following at the prompt. pre_upgrade_prep.pl <system>_dev/dbc,[dbc password] <SYSTEM>_DEV.log

[The output for Steps 2, 4, 6 will be a set of scripts specific to the environment. The output files are listed below:

  1. udd_out.rpt
  2. dbql_out.rpt
  3. dbc_role.rpt
  4. reservedwords.rpt
  5. rep_recovery.rpt
  6. pwdchgdate.rpt
  7. mds_recovery.rpt
  8. trigger_out.rpt
  9. upgrade_estimate.rpt
  10. sp_nospllist.txt
  11. test_<env>.log

The number of output files may change with future releases of Teradata but the list above is current for Teradata 12.]

3. Step 3 – When the script has completed copy the DEV output files, including the LOG file into a DEV specific directory as the output files will be overwritten by subsequent runs of the file.

4. Step 4 – For the <SYSTEM> PROD system enter the following at the prompt.

pre_upgrade_prep.pl <system>_PROD/dbc,[dbc password] <SYSTEM>_PROD.log

5. Step 5 – When the script has completed copy the PROD output files, including the LOG file into a PROD specific directory as the output files will be overwritten by subsequent runs of the file.

6. Step 6 – For the <SYSTEM> DR system enter the following at the prompt.

pre_upgrade_prep.pl <system>_DR/dbc,[dbc password] <SYSTEM>_DR.log

7. Step 7 – When the script has completed copy the DR output files, including the LOG file into a DR specific directory as the output files will be overwritten by subsequent runs of the file.

8. Step 8 – Zip the 3 folders and email them to the Td CS engineer.

The following is a list of scripts that are used in upgrading or migrating to Teradata Database Release 12 and their purpose.

Name Purpose File Name Date
Reserved Words Script Finds V12 Reserved Words check_reserved_words.bteq 09/17/07
Reserved Words Short List Shows new words for 12 reserved_words_short_list.txt 09/17/07
Stored Procedures Inspection Script Finds Teradata Stored Procedures

That will not recompile nospl.bteq 08/18/04
Triggers Inspection Script Finds triggers that may violate 6.x rules trigger_inspection.bteq 06/13/06
Upgrade Estimate Script Produces upgrade estimate data for v12estimator.xls upgrade_estimate.bteq 06/13/06
UDD Character Columns Inspection Script Inspects UDD character columns before upgrade 12.x udd_inspec_standard.bteq

udd_inspec_japanese.bteq 09/22/08

09/22/08
Store

Procedures

Source files

for UDD SPs with dynamic

SQL instead of

repeating submit

SQL statements chkambiguous

chkdupnames

chkfor1a

chksbc

chktranserr

concatstr

instmptbl

chkforb5 07/07/08

09/17/07

09/17/07

07/07/08

07/07/08

07/07/08

07/07/08

09/17/07
UDD

Character

Sets

Inspection

Script Inspect character

sets installed

on the system

before pre-upgrade udd_check_charset.bteq

02/06/08
Pre-Upgrade

Preparation

Script Inspects DBS readiness

before upgrade by

running the following

scripts automatically:

check_reserved_words.bteq

trigger_inspection.bteq

nospl.bteq

upgrade_estimate.bteq

udd_inspec_standard.bteq on non-Japanese system

udd_check_charset.bteq on Japanese system

udd_inspec_japanese.bteq on Japanese system pre_upgrade_prep.pl 11/24/08

Collect Statistics

Fill this in

TPUMP Array Support

Fill in later