Unix tool CNSTERM fuzz !!

The cnsterm tool is the PDE Console Subsystem Terminal. You can use it
to display console utility output without starting the xdbw (Database
Window) program. The cnsterm tool is executed from the UNIX command
line, and you do not need to have X-Windows configured to use it, as you
do with the xdbw. The cnsterm tool displays a single window at a time.
When using cnsterm the only command line option available is the
Database Window partition number. Partition numbers 1 through 4 are
the Database Window console utility windows, partition 5 is the Database
I/O window, and partition 6 is the Database Window Supervisor screen.
You will typically start cnsterm in window 6, and then move to that window
to start any console utility programs.
You must be logged on as root to execute cnsterm. To use cnsterm from
the UNIX command line:
1. Logon as Root.
2. Enter cnsterm 6 on the UNIX command line. This will display the
Database Window Supervisor screen.
3. To start a console utility, type in the start command syntax (i.e., start
qryconfig). The display will tell you which partition qryconfig was
started in (i.e., Window 1).
4. Press the Delete key and enter cnsterm 1. You will now see the
contents of Window 1 (i.e., the qryconfig program) and you may now
enter commands to execute the qryconfig utility.
Note: To move between windows, use the Delete key to get a prompt,
and enter cnsterm n, where n is the number of the window you want.
5. Press the DELETE key and enter cnsterm 6 to move back to the
Supervisor display.
6. Type stop 1 to terminate the utility that was running in Window 1 (i.e.,
qryconfig).
You may start as many utilities as there are partitions available in the
Database Window.

Teradata Certification - 2nd Shot Exam Promotion

An oppertunity for us :-))

Below extracted from the Teradata Site (https://www.teradata.com/t/go.aspx?id=145533)

2nd Shot Exam Promotion

If at first you don't succeed, try again…on us! Give yourself the best chance for success with an extra opportunity to pass a Teradata Certification exam!

Promotion Terms and Conditions:

• The 2nd Shot Exam Promotion that begins February 28, 2006, is offered worldwide on all Teradata V2R5 Certification Exams (NR0-011 through NR0-017).
• Take your initial exam between the dates of February 28 - May 31, 2006, at any Thomson Prometric Testing Center worldwide, in order to be eligible for a free re-take.
• You are eligible for a free re-take exam if you do not pass the initial exam. Thomson Prometric will verify the exam status prior to allowing a free re-take.
• Only one free re-take is allowed for an unsuccessful exam. The identical exam number is required when you register to re-take the exam.
• You must wait 14 calendar days (after the unsuccessful exam) before re-taking the same exam.
• The free re-take exam must be taken by June 30, 2006. No free re-takes after that date.
• To register for your free re-take exam, you must CALL a Thomson Prometric Regional Call Center. On-line registrations through Prometric.com will not be valid for this promotion. Regional Call Center telephone numbers are as follows:
  • Toll Free US & Canada: 1-877-887-6868
  • Latin America: 1-443-751-4300
  • Asia Pacific: 61-2-9640-5830
  • Europe/Middle East/Africa: 31-320-239-800
  • Japan: 0120-387737

Questions?

• Refer to the Frequently Asked Questions (FAQ's).
• Email the Teradata Certified Professional Program Team at: certification.teradata@ncr.com
• Leave a voicemail at 1-800-845-2273 (OPTION 2), or 1-937-445-4443.

BTEQ script to fetch table DDL's

/* This BTEQ script fetches the table DDL's for the current DB */
/* Make sure you set the database before running the script */
/* Copy contents and paste it in a text file (eg getDDL.scp) */
/* in the prompt # bteq < getDDL.scp (to execute) */

.SET SIDETITLES OFF
.SET WIDTH 254
.SET QUIET ON

.os IF EXIST showDDL.tmp del showDDL.tmp

.EXPORT REPORT file=showDDL.tmp

SELECT '.SET TITLEDASHES OFF' (TITLE '');

/* Generate Table DDL's */

SELECT 'SHOW TABLE '||TABLENAME||';' (TITLE '')
FROM DBC.TABLES
WHERE DATABASENAME = DATABASE AND TABLEKIND = 'T'
ORDER BY 1;

/* Generate Views DDL's */
SELECT 'SHOW VIEW '||TABLENAME||';' (TITLE '')
FROM DBC.TABLES
WHERE DATABASENAME = DATABASE AND TABLEKIND = 'V'
ORDER BY 1;


/* Generate Macro DDL's */
SELECT 'SHOW MACRO '||TABLENAME||';' (TITLE '')
FROM DBC.TABLES
WHERE DATABASENAME = DATABASE AND TABLEKIND = 'M'
ORDER BY 1;


/* Generate Stored Procedure DDL's */
SELECT 'SHOW PROCEDURE '||TABLENAME||';' (TITLE '')
FROM DBC.TABLES
WHERE DATABASENAME = DATABASE AND TABLEKIND = 'P'
ORDER BY 1;


SELECT '.DEFAULTS' (TITLE '');

SELECT '.EXPORT RESET' (TITLE '');


.EXPORT RESET

.os IF EXIST DDLS.txt del DDLS.txt

.EXPORT REPORT file=DDLS.txt

.run file = showDDL.tmp

.os IF EXIST showDDL.tmp del showDDL.tmp/Q

Check if 2 DB in Sync

I created the below script to test if tables in two db's are in sync.

/***************************************************/
/* Script to compare Tables in two databases */
/* Steps to follow : */
/* 1. Replace dp_tedw with name of first DB */
/* 2. Replace ed1_tedw with name of second DB */
/* 3. Save AS the file */
/* 3. Logon to BTEQ (.LOGON tdpid/usr,pwd) */
/* 4. Execute script (.RUN FILE = */
/* 5. Report file DBCompare.out */
/***************************************************/


.SET DEFAULTS
.SET WIDTH 254
.SET QUIET ON
.SET ECHOREQ ON

.OS IF EXIST DBCompare.out DEL DBCompare.out

.EXPORT REPORT FILE=DBCompare.out

SELECT '/******************************************/' (TITLE'');
SELECT '/* Database compared dp_tedw AND ed1_tedw */' (TITLE'');
SELECT '/* DATE : '||DATE||' TIME : '||TIME||' */' (TITLE'');
SELECT '/******************************************/' (TITLE'');

.REPEAT 2
SELECT '' (TITLE'');

/* Identify list of Objects in dp_tedw AND not in ed1_tedw */
SELECT '/******************************************/' (TITLE'');
SELECT '/* LIST OF OBJECTS IN dp_tedw AND NOT IN ed1_tedw */' (TITLE'');
SELECT '/******************************************/' (TITLE'');

SELECT tablename "dp_tedw TableName"
FROM dbc.tables
WHERE databasename = 'dp_tedw'
AND tablename NOT IN (SELECT tablename FROM dbc.tables WHERE databasename = 'ed1_tedw')
WITH COUNT(*) (TITLE '*** No. of Tables in dp_tedw AND NOT IN ed1_tedw')
ORDER BY 1
;

.REPEAT 2
SELECT '' (TITLE'');

/* Identify list of objects in ed1_tedw AND not in dp_tedw */
SELECT '/******************************************/' (TITLE'');
SELECT '/* LIST OF OBJECTS IN ed1_tedw AND NOT IN dp_tedw */' (TITLE'');
SELECT '/******************************************/' (TITLE'');

SELECT tablename "ed1_tedw TableName"
FROM dbc.tables
WHERE databasename = 'ed1_tedw'
AND tablename NOT IN (SELECT tablename tbl FROM dbc.tables WHERE databasename = 'dp_tedw')
WITH COUNT(*) (TITLE '*** No. of Tables in ed1_tedw AND NOT IN dp_tedw')
ORDER BY 1
;

.REPEAT 2
SELECT '' (TITLE'');

/* Identify the common entities */
SELECT '/******************************************/' (TITLE'');
SELECT '/** LIST OF COMMON OBJECTS IN dp_tedw AND ed1_tedw **/' (TITLE '');
SELECT '/******************************************/' (TITLE'');

SELECT tablename "Common Tables"
FROM dbc.tables
WHERE databasename = 'dp_tedw'
AND tablename IN (SELECT tablename FROM dbc.tables WHERE databasename = 'ed1_tedw')
WITH COUNT(*) (TITLE '*** No. of Common Tables')
ORDER BY 1
;


/* CREATE volatile table to hold common tables */
CREATE VOLATILE TABLE _common_tables
(
dbname CHAR(30)
,tablename CHAR(30)
)
UNIQUE PRIMARY INDEX (dbname, tablename)
ON COMMIT PRESERVE ROWS
;

/* Identify the common tables */
INSERT INTO _common_tables
SELECT databasename,tablename tbl
FROM dbc.tables
WHERE databasename = 'dp_tedw'
AND tbl IN (SELECT tablename tbl FROM dbc.tables WHERE databasename = 'ed1_tedw')
;


INSERT INTO _common_tables
SELECT databasename,tablename tbl
FROM dbc.tables
WHERE databasename = 'ed1_tedw'
AND tbl IN (SELECT tablename tbl FROM dbc.tables WHERE databasename = 'dp_tedw')
;


/* Identify the non-matching fields */
CREATE VOLATILE TABLE _fieldChk
(
DbName CHAR(30) NOT NULL
,TableName CHAR(30) NOT NULL
,FieldName CHAR(30)
,FieldType CHAR(2)
,MaxLength INTEGER
,FieldFormat CHAR(30)
,FieldDefault VARCHAR(30)
,FieldNull CHAR(1)
)
UNIQUE PRIMARY INDEX (dbname, tablename, fieldname)
ON COMMIT PRESERVE ROWS
;


INSERT INTO _fieldChk
SELECT cols.DatabaseName
,cols.TableName
,cols.ColumnName
,cols.ColumnType
,cols.ColumnLength
,cols.ColumnFormat
,cols.DefaultValue
,cols.Nullable
FROM dbc.columns cols, _common_tables tmp
WHERE cols.databasename = tmp.dbname
AND cols.tablename = tmp.tablename
;

.REPEAT 2
SELECT '' (TITLE'');


/* Matching columns but different field length */
SELECT '/**************************************************/' (TITLE'');
SELECT '/* LIST OF MATCHING FIELDS FROM THE COMMON TABLES */' (Title '');
SELECT '/* WITH DIFFERENT FIELD LENGTH */' (Title '');
SELECT '/**************************************************/' (TITLE'');

.SET SKIPLINE ON 1

.SET SUPPRESS ON 1,2

SELECT
t1.TableName
,t1.FieldName
,t1.FieldType
,der.FieldType
,min(der.MaxLength ) "Len in Fld1"
,max(t1.MaxLength ) "Len in Fld2"
FROM
_fieldChk t1
,(SELECT
max(DbName) dbname
,TableName
,FieldName
,FieldType
,MaxLength
, count(*) cnt
FROM _fieldChk
GROUP BY
TableName
,FieldName
,FieldType
,MaxLength
HAVING cnt <> 2
) der
WHERE t1.tablename = der.tablename
AND t1.dbname <> der.dbname
AND t1.FieldName = der.FieldName
AND t1.FieldType = der.FieldType
GROUP BY 1,2,3,4
ORDER BY 1,2
;

.REPEAT 2
SELECT '' (TITLE'');

.SET SKIPLINE OFF

/* Matching columns but different field constraint */
SELECT '/**************************************************/' (TITLE'');
SELECT '/* LIST OF MATCHING FIELDS FROM THE COMMON TABLES */' (Title '');
SELECT '/* WITH DIFFERENT FIELD FORMAT CONSTRAINT */' (Title '');
SELECT '/**************************************************/' (TITLE'');

.SET SKIPLINE ON 1

.SET SUPPRESS ON 1

SELECT
t1.TableName
,t1.FieldName
,t1.FieldType
,der.FieldType
,min(der.FieldFormat ) "Val in Fld1"
,max(t1.FieldFormat ) "Val in Fld2"
FROM
_fieldChk t1
,(SELECT
max(DbName) dbname
,TableName
,FieldName
,FieldType
,FieldFormat
,count(*) cnt
FROM _fieldChk
GROUP BY
TableName
,FieldName
,FieldType
,FieldFormat
HAVING cnt <> 2
) der
WHERE t1.tablename = der.tablename
AND t1.dbname <> der.dbname
AND t1.FieldName = der.FieldName
AND t1.FieldType = der.FieldType
GROUP BY 1,2,3,4
ORDER BY 1,2
;

.REPEAT 2
SELECT '' (TITLE'');

.SET SKIPLINE OFF

/* Matching columns but different field constraint */
SELECT '/**************************************************/' (TITLE'');
SELECT '/* LIST OF MATCHING FIELDS FROM THE COMMON TABLES */' (Title '');
SELECT '/* WITH DIFFERENT FIELD NULL CONSTRAINT */' (Title '');
SELECT '/**************************************************/' (TITLE'');

.SET SKIPLINE ON 1

.SET SUPPRESS ON 1

SELECT
t1.TableName
,t1.FieldName
,t1.FieldType
,der.FieldType
,min(der.FieldNull ) "Val in Fld1"
,max(t1.FieldNull ) "Val in Fld2"
FROM
_fieldChk t1
,(SELECT
max(DbName) dbname
,TableName
,FieldName
,FieldType
,FieldNull
,count(*) cnt
FROM _fieldChk
GROUP BY
TableName
,FieldName
,FieldType
,FieldNull
HAVING cnt <> 2
) der
WHERE t1.tablename = der.tablename
AND t1.dbname <> der.dbname
AND t1.FieldName = der.FieldName
AND t1.FieldType = der.FieldType
GROUP BY 1,2,3,4
ORDER BY 1,2
;


.REPEAT 2
SELECT '' (TITLE'');

.SET SKIPLINE OFF

/* Matching columns but different field constraint */
SELECT '/**************************************************/' (TITLE'');
SELECT '/* LIST OF MATCHING FIELDS FROM THE COMMON TABLES */' (Title '');
SELECT '/* WITH DIFFERENT FIELD DEFAULT CONSTRAINT */' (Title '');
SELECT '/**************************************************/' (TITLE'');

.SET SKIPLINE ON 1

.SET SUPPRESS ON 1

SELECT
t1.TableName
,t1.FieldName
,t1.FieldType
,der.FieldType
,min(COALESCE(t1.FieldDefault,'NULL')) (NAMED "Val in Fld1")
,max(COALESCE(der.FieldDefault,'NULL')) (NAMED "Val in Fld2")
FROM
_fieldChk t1
,(SELECT
max(DbName) dbname
,TableName
,FieldName
,FieldType
,FieldDefault
,count(*) cnt
FROM _fieldChk
GROUP BY
TableName
,FieldName
,FieldType
,FieldDefault
HAVING cnt <> 2
) der
WHERE t1.tablename = der.tablename
AND t1.dbname <> der.dbname
AND t1.FieldName = der.FieldName
AND t1.FieldType = der.FieldType
GROUP BY 1,2,3,4
ORDER BY 1,2
;

.REPEAT 2
SELECT '' (TITLE'');

.SET SKIPLINE OFF

/* Non matching columns */
SELECT '/******************************************************/' (TITLE'');
SELECT '/* LIST OF NON-MATCHING FIELDS FROM THE COMMON TABLES */' (Title '');
SELECT '/******************************************************/' (TITLE'');

.SET SKIPLINE ON 2

.SET SUPPRESS ON 1,2

SELECT
max(DbName) DatabaseName
,TableName
,FieldName
,FieldType
FROM _fieldChk
GROUP BY
TableName
,FieldName
,FieldType
HAVING count(*) <> 2
ORDER BY 1,2
;


.REPEAT 2
SELECT '' (TITLE'');

.SET SKIPLINE OFF

SELECT '/*******************************************************/' (TITLE'');
SELECT '/* LIST OF NON-MATCHING Indexes FROM THE COMMON TABLES */' (Title '');
SELECT '/*******************************************************/' (TITLE'');

/* Identify the non-matching PI */
CREATE VOLATILE TABLE _indexChk
(
DbName CHAR(30) NOT NULL
,TableName CHAR(30) NOT NULL
,FieldName CHAR(30)
,IndexType CHAR(1)
,UniqueFlag CHAR(1)
,FieldPosition SMALLINT
)
UNIQUE PRIMARY INDEX (dbname, tablename, fieldname, IndexType)
ON COMMIT PRESERVE ROWS
;

INSERT INTO _indexChk
SELECT
DatabaseName
,TableName
,ColumnName
,IndexType
,UniqueFlag
,ColumnPosition
FROM dbc.indices
WHERE tablename in ( SELECT DISTINCT tablename FROM _common_tables )
AND databasename in ( SELECT DISTINCT dbname FROM _common_tables )
;

.SET SKIPLINE ON 2

.SET SUPPRESS ON 1,2

SELECT
ick.DbName
,ick.TableName
,ick.FieldName
,ick.IndexType
,ick.UniqueFlag
,ick.FieldPosition
FROM _indexChk ick
JOIN (SELECT tablename, fieldname
FROM _indexChk
HAVING COUNT(*) <>2
GROUP BY 1,2) der
ON ick.tablename = der.tablename
AND ick.fieldname = der.fieldname
ORDER BY 1,2,4,6
;

.REPEAT 2
SELECT '' (TITLE'');

.SET SKIPLINE OFF

SELECT '/*******************************************************/' (TITLE'');
SELECT '/* LIST OF NON-MATCHING Sequence/Order FROM THE COMMON TABLES */' (Title '');
SELECT '/*******************************************************/' (TITLE'');

.SET SKIPLINE ON 2

.SET SUPPRESS ON 1,2

SELECT
der1.databasename
,der1.tablename
,der1.columnname
,der1.columnid "Seq in dp_tedw"
,der2.columnid "Seq in ed1_tedw"
FROM
(SELECT
databasename
,tablename
,columnname
,columnid
FROM dbc.columns
WHERE tablename in ( SELECT DISTINCT tablename FROM _common_tables )
and databasename in ( SELECT DISTINCT dbname FROM _common_tables )
) der1
,(SELECT
databasename
,tablename
,columnname
,columnid
FROM dbc.columns
WHERE tablename in ( SELECT DISTINCT tablename FROM _common_tables )
and databasename in ( SELECT DISTINCT dbname FROM _common_tables )
) der2
WHERE der1.databasename = 'dp_tedw'
and der1.tablename = der2.tablename
AND der1.columnname = der2.columnname
AND der1.columnid <> der2.columnid
ORDER BY 1,2
;

.REPEAT 2
SELECT '' (TITLE'');

.SET SKIPLINE OFF

SELECT '/*******************************************************/' (TITLE'');
SELECT '/* -------------------- End of File ------------------ */' (Title '');
SELECT '/*******************************************************/' (TITLE'');


.EXPORT RESET

.SET DEFAULTS

DROP TABLE _common_tables;

DROP TABLE _fieldChk;

DROP TABLE _indexChk;



/* End of Script */

Check MiniCylinderPacks

/* Identify mini cylinder packs */
/*
5141 Mini-Cylinder Pack freed cylinders in anticipation of their use
5142 Mini-Cylinder Pack freed cylinders while tasks were waiting for them
5143 Mini-Cylinder Pack was not able to free any cylinders
5166 Defragment of cylinder[s] occurred
*/
select thedate
,sum (case when text like '5141%' then 1
else 0
end) as E5141_Anticipation
,sum (case when text like '5142%' then 1
else 0
end) as E5142_Tasks_Waiting
,sum (case when text like '5143%' then 1
else 0
end) as E5143
from dbc.software_event_log
where text like '%mini-cylinder%' and text like '514%'
group by 1
order by 1 desc;

Recover left over Spool space in Teradata

To Identify the left over spool, use the following SQL.

/* LEFT OVER SPOOL */
sel databasename
,sum(currentspool)
from DBC.DiskSpace
where databasename NOT IN
(sel username from DBC.SessionInfo)
group by 1 having sum(currentspool) > 0
order by 2 desc;

/* Left over spool - by vprocs */
SELECT DATABASENAME, VPROC, CURRENTSPOOL
FROM DBC.DISKSPACE
WHERE DATABASENAME NOT IN (SEL USERNAME FROM DBC.SESSIONINFO)
AND CURRENTSPOOL > 0
ORDER BY 1,2 with sum(currentspool);


To clear the same follow the proc below.
1. Login as root in the unix box (NTPA)
2. rlogin (connect to the gateway/active node)
3. cnsterm 6 (open a server session)
4. start updatespace (start the utility)
5. Ctrl+d to exit this window
6. cnsterm x (where x is whatever window it's started in - output from the cnsterm 6 screen)

You will see screen with

The format of the input command is:

UPDATE [SPOOL | TEMPORARY | ALL] SPACE FOR {ALL DATABASES | dbname} ;

7. If u decide to do it db by db, then type continue after each update.
8. type quit to exit
9. ctrl+d to exit the window
10. cnsterm 6
11. stop x (where x is the update space util run window)


Log from the activity ;

Enter Command
> update spool space for myDb;
update spool space for myDb;

Updating space for myDb
Space updated for myDb
Enter QUIT or CONTINUE.
> continue
continue


Enter Command
> update spool space for myDb2;
update spool space for myDb2;

Updating space for myDb2
Space updated for myDb2
Enter QUIT or CONTINUE.
> quit