What is EPOCH ?

EPOCH is time in seconds since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970 ( 1970/01/01 00:00:00 UTC). Or the epoch is Unix time 0 (midnight 1/1/1970), but 'epoch' is often used as a synonym for 'Unix time'. EPOCH time is widely used in every field of IT sector (specially in unix like OS ) where we have to keep difference is generation of something based on time.

Delete Duplicate Rows in DB2 Database

Many times and Many places, this question is being asked :-) that How to delete the duplicate row from a table in different DBs. Here, we will see How to do this in DB2 DB.

Create table from another table in DB2 database

We can create a table from another table definition but without data in DB2. We need write a another sql to insert the data into newly created table.

Let's see how to do this --

DataStage Parallel job: Retrieve sql codes on a failed upsert

When an enterprise database stage such as DB2 or Oracle is set to upsert it is possible to create a reject link to trap rows that fail any update or insert statements. By default this reject link holds just the columns written to the stage, they do not show any columns indicating why the row was rejected and often no warnings or error messages appear in the job log.

A SQL Client Tool - TeraData Studio Express

Teradata Studio Express is a graphical Java program, developed on the Eclipse Rich Client Platform (RCP),  that will allow you to view the structure of a JDBC compliant database, browse the data in tables, issue SQL commands etc.
This is a nice tool which can connect many DBs like Aster database, DB2 for LUW, DB2 for i5/OS, DB2 for z/OS, Oracle, SQL Server, Generic JDBC connection and of course TeraData DB.

List of Environment Variables in DataStage

General Job Administration

APT_CHECKPOINT_DIR
APT_CLOBBER_OUTPUT
APT_CONFIG_FILE
APT_DISABLE_COMBINATION
APT_EXECUTION_MODE
APT_ORCHHOME
APT_STARTUP_SCRIPT
APT_NO_STARTUP_SCRIPT
APT_STARTUP_STATUS
APT_THIN_SCORE

DB2 commands Cheat Sheet

DB2 System Commands

    DB2LEVEL -- checks version of DB2 installed.
    DB2ILIST -- lists all instances installed
    DB2CMD -- opens a command line processor
    DB2CC -- opens db2 control center
    DB2LICM -l -- gets db2 type.

Command Line Processor Commands

DB2 Fundamentals

DB2 for Linux, UNIX, and Windows (US)

Industry-leading performance for mixed workloads on distributed systems, offering unparalleled efficiencies for staffing and storage.

DB2 for z/OS

The database software gold standard for reliability, availability, and scalability. Optimized for SOA, CRM and data warehousing.

14 design tips for better performance in Datastage

1) Avoid unnecessary type conversions: set the OSH_PRINT_SCHEMAS environment variable to verify that run time schemas match the job design column definitions. If you are using stage variables on a Transformer stage, ensure that their data types match the expected result types.

2) Use Transformer stages sparingly and wisely. Transformer stages can slow down your job. Do not have multiple stages where the functionality could be incorporated into a single stage, and use other stage types to perform simple transformation operations

How to split source column into multiple target columns ( full name to first and Last)

Approach:

CREATE SET TABLE test

( 

fullname varchar(30)

);

INSERT INTO test12 ('nitin raj');

INSERT INTO test12 ('nitin agarwal');

INSERT INTO test12 ('abhishek gupta');

List the tables in DB2 LUW & z/OS

List the tables in DB2 LUW

list tables for all

list tables for schema <schema_name>

or

select * from syscat.tables ;

Get DB2 Version

Method #1. Connect to the database from the command line


 

DataStage Unix processes

Following are unix process list for a healthy DS system (Engine + Services + Metadata repository)

Process: /opt/IBM/InformationServer/ASBNode/apps/jre/bin/java Class: com.ascential.acs.logging.agent.LoggingAgentSocketImpl

XMeta DB : Datastage Repository - 2

Prior to Datastage version 8, the datastage repository information was stored in files(uv). From version 8, the repository information is also stored in database. Since the information is available in database, it is easier to write queries and find the details. During the installation you will have the option to install the repository either in DB2 or in Oracle.

Usually, the datastage repository is built on DB2 database. because Db2 comes with the Information Server Software by defalt.

A word of caution: Do not try to alter the XMETA repository contents as it may have adverse effects.


Here is the Part 1 of this tutorial :   XMeta DB : Datastage Repository - 1

DB2 query to select first or last N rows

There may be instances when you wish to select first or last N rows.

You can use the following query to limit the number of rows retrieved using select command.

First N rows

Configuring DB2 database connectivity in a parallel environment in DataStage

Configure DataStage to run on all processing nodes that function as DB2 servers.

Steps

To configure InfoSphere DataStage to access IBM DB2 servers, include a node definition for each IBM DB2 server node in the configuration file. Also, complete the following steps.

XMeta DB : Datastage Repository

Prior to Datastage version 8, the datastage repository information was stored in files(uv). From version 8, the repository information is also stored in database. Since the information is available in database, it is easier to write queries and find the details. During the installation you will have the option to install the repository either in DB2 or in Oracle.

Usually, the datastage repository is built on DB2 database. because Db2 comes with the Information Server Software by defalt.

A word of caution: Do not try to alter the XMETA repository contents as it may have adverse effects.

Reclaiming disk space from DB2 XMeta tablespace after deleting projects

The DB2 XMeta database has grown over time as more development projects were created. After deleting all the old projects, the tablesize of the XMeta database has not reduced. The disk space occupied by the DB2 XMeta database is still the same. What DB2 commands can be run to reduce the XMeta tablespace on the hard disk?

Deleting projects from the DS Administrator frees space on the XMeta database but this does not translate into a reduction in the XMeta tablespace occupied on the hard disk.

Resetting the XMETA password in InfoSphere Information Server

On Unix/Linux:

IBM/InformationServer/ASBServer/bin/AppServerAdmin.sh -db -user xmeta -password <your password>

How to reduce the current size of IBM InfoSphere Information Server XMETA database

Xmeta DB is used by DataStage itself for doing its work. But sometimes when there is lot of project on DataStage then it need to be monitored. 
In this case, Xmeta size is growing rapidly so here is some steps which can solve this problem a bit.


The first method is from within db2 (the system should be quiesced for this approach):

   1.  Start the db2 command line processor:

    cd /opt/IBM/db2/V9/bin

    ./db2