Friday, May 29, 2015

What you need to know about DBC in Teradata?

As a database developer you may think DBC* is for database administrator to care about, not for you as a database developer.

However, knowing about DBC can save your time when it come to query like. e.g. you need to  know all the tables in your database? or Column names in a table or existing views in a database. Sometimes, you may need to anonymous some of the columns in a few tables that you need to do using DBC.

You could say, I can see all table names by browsing the database table and can copy and paste names of all the tables. Yes, you can do it but that is time consumable and manual process. Would not it be nice if you can get all the table names by writing two lines of code.

Lets say, you want to find out all the tables name in your database then SQL will look like below:

Select * from dbc.tables where tablekind='T' and databasename='STUDENT103'

which will return below result (part of the full result):
Fig 1: Tables list of the database

To find out the view names from the database then SQL will look like below:

Select * from dbc.tables where tablekind='V' and databasename='STUDENT103'

Part of the result set look like below:
Fig 2: View list of the database(database contains only one view)

To get all the column names of a table of database, you need to write below SQL:

SELECT DatabaseName,TableName,ColumnName
FROM DBC.Columns
WHERE DatabaseName='STUDENT103'

Result of the above code will look like below:

Fig 3: List of columns in a table

By using DBC you can get not only column, table, view names but also more attributes like who created the table or view, when it's created and so on.

Note: DBC is the superuser on Teradata database

Wednesday, May 13, 2015

A newly developed job in SAS DI Studio: Why target table not found after Checked In?

When you start working with new tool you will find the tool is buggy (you assume so.. but actually most of the cases it’s you who don’t know all features of the tool).  However, a few cases tool have it’s known issue or can’t report the error message properly.

If you are new to SAS DI, I am sure you will find this post interesting.

Let’s start with a ETL job that which get data from an external file and loading into Teradata table.

       Fig 1: An example job that get data from external file and load into Teradata table

When job is completed we do checked in and so that other developer can review it and can deployed to Production.

Check In Window Look like below:

                                        Fig 2:  Check In Window

After clicking Check In you will find below three steps to do check In the job.

Fig 3: Title and description at while check in.

  Fig 4: Verify the objects that you are checking in

Fig 5: Summary of Check In Wizard

After completing the check In when you tried to open the job you will find below error:
Fig 6: Missing Objects

By looking at the information message you can't identify what has changes until you look and observe your job is missing two objects one is external source file and other is target Terdata table. 

But you have checked in the job, why then those objects are missing??

Well, only by checking  the job you can't expect all objects will be check in automatically. You must need to do check In source file and target table then you will find the job as it should be.

Tips: It's always good idea to do check In even if the job is not completed and check In all the objects related to job.