BTEQ TIPS in Teradata

Share via:

Dear Readers,

In this article, we will see BTEQ TIPS 

 

Question :How to display more than 100 columns in BTEQ ?

Answer:

BTEQ Export defaults to a maximum of 100 columns to be SELECTED in the SELECT statement. Hence, when we try to run a query that contains more than 100 columns, it gives the below warning and shows only the first 100 columns.

* Warning: Report has more than 100 column(s).Only the first 100 column(s) will be displayed.

 

Solutionis to use the following BTEQ command which will change the maximum number of rows (n1, default 1000000) and columns (n2, default 100) to be returned:

Example :

The above command will return 100 rows and 200 columns.

 

Question :How to import/insert file into BLOB(Binary Large Object)/CLOB(Character Large Object) XML column via BTEQ ?

Answer:

Step 1:Assuming below is the DDL of the target table where we want to insert data into BLOB column

 

Step 2 :Place the import source file and Binary data file on your work directory. Here we will be inserting two jpg files.

Step 3 :Using .import command, run the INSERT SQL

/*LOBCOLS specifies the # of LOB columns*/

 

Step 4 :Check if the BLOB column has Binary file/data inserted

Question :How to view all data on one page in BTEQ?

Answer:

The command PAGELENGTH command can be used that helps in resizing page length as per requirement.

An asterisk (*) represents a single page with unlimited number of lines.

Each query will produce one page with a length equal to the number of lines needed to print that query’s entire result set and possibly a header, footer, column titles, and titledashes.

Note: The FORMAT setting should be ON for the PAGELENGTH setting to have any effect on formatting report form output.

The PAGELENGTH command can also be used in a Teradata SQL macro.

Syntax:

where ‘n’ is the maximum number of lines on a page.Default value is 55.

Below is an example to help us understand this better.

With the help of PAGELENGTH command, we will set the maximum page length at 75 lines and at 20 lines for the same select operation:

In response to the second select operation BTEQ formats the results, prints the footer atline 20, and ejects the page as follows:

*** Query completed. 5 rows found. 3 columns returned.

(BTEQ counts to line 20)

Bottom of Page

 

Question :How to set a specific number of sessions for a BTEQ job ?

Answer:

We can set the desired number of sessions for aBTEQ job using .set sessions command

 

Question :What is the maximum number of concurrent sessions we can run from BTEQ ?

Answer:

Maximum number of concurrent BTEQ sessions for a job can be 200.

 

Question :What happens when the number of BTEQ connections exceed its limit ?

Answer:

Considering the below BTEQ script where we specify 300 sessions ( which is more than max limit of 200) :

 

Question :How to check the current number of simultaneous BTEQ sessions established from a user ?

Answer:

If you want to check the number of sessions at later point of time you can use the same query but on dbc.logonoff view.

Question :How to Kill BTEQ process from UNIX ?

Answer:

Use the below command to see all the running BTEQ processes

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment