Thursday 20 December 2012

No Primary Index Table in Teradata

NOPI or No Primary Index tables were introduced to make the Loading activities faster. We insert data into the table based on Random distribution. Since, no hashing is involved, data load will be significantly faster.

But, developers should take ample precaution before declaring a table as NOPI.

Example:

CREATE MULTISET TABLE PRD_DB.FACT_BSKT_SUM ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      DAY_NBR DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      LOCN_NBR INTEGER NOT NULL,
      VBS_NBR INTEGER NOT NULL,
      TRS_CNT INTEGER NOT NULL,
      TTL_BSKT_AMT DECIMAL(15,2) NOT NULL,
      TTL_BSKT_UN_QT DECIMAL(15,4) NOT NULL)
NO PRIMARY INDEX ;

Disadvatages:

Data in a NoPI table is stored by random distribution. So, when a SELECT runs on the NoPI table, we are forced to do a Full table scan.
In the absence of a Primary Index or Hash Value, a full table scan based on all-AMPs is the only way to fetch data.
Now, a full table scan uses the Master Index and Cylinder Index of the data block, instead of using any Hashing Index or USI. So, joins with NoPI tables can become significantly slow.


Let us know your topic of interest. And Please Donate if you are helped using the button on the right!!


Fallback feature in Teradata - Ensure Access to data all the time

What is Fallback in Teradata?
FALLBACK ensures that a second copy of each row inserted into a table is stored on another AMP in the same cluster. This is done in case the AMP goes down or the disks fail. There are usually four AMPs grouped together in a cluster. FALLBACK allows for processing to continue in the event that an AMP is lost. As a matter of fact, FALLBACK allows for the loss of one AMP in each cluster. If 2 AMPS are lost, then the system comes to a halt.



The cost for FALLBACK is that twice as much disk storage space is used for row storage as compared to not using FALLBACK. The cost is also twice the I/O on inserts, updates, and deletes because there are always two copies to write. One copy goes to the primary AMP where it belongs and the other copy goes to different AMP in the same cluster. Since Teradata AMPs operate in parallel; so, it does not take twice as long to store a duplicate row. Both rows are placed on their respective AMPs at nearly the same time.


To overcome the Storage disadvantage of Fallback, TD also allows Virtual Processor migration. Since AMP and PE are both software modules, they can be migrated to a different node.


The VProc migration provides fault-tolerance without using much extra space.
The below image explains a PE + AMP migration.






FALLBACK is an optional feature. Only important tables are fallback protected.

Storing TIME(0) or Time(6) columns using SQL Assistant can be tricky - Learn How

Discrepancy in storing TIME(0) columns using SQL Assistant-
The default driver available to SQL Assistant is ODBC, we have to manually set it to Teradata.NET driver if we have TIME(0) columns in the create table statement.

Example
ODBC driver:
CREATE SET TABLE alex_arp_tbls_prd.date_test1 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      val_date INTEGER FORMAT '99:99:99')
PRIMARY INDEX ( val_date );


Teradata.Net Driver:
CREATE SET TABLE alex_arp_tbls_prd.date_test2 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      val_date TIME(0))
PRIMARY INDEX ( val_date );


Developers should be really careful while migrating code from SQL Assistant to Bteq mode.

Tuesday 18 December 2012

Locks in Teradata - how to effectively do Insert and Select on the same table:


There are 4 kind of locks in Teradata:


Exclusive - Exclusive locks are placed when a DDL is fired on the database or table, meaning that the Database object is undergoing structural changes. Concurrent accesses will be blocked.


Compatibility: NONE



Write - A Write lock is placed during a DML operation. INSERT, UPDATE and DELETE will trigger a write lock. It may allow users to fire SELECT queries. But, data consistency will not be ensured.


Compatibility: Access Lock - Users not concerned with data consistency. The underlying data may change and the user may get a "Dirty Read"



Read - This lock happens due to a SELECT access. A Read lock is not compatible with Exclusive or Write locks.


Compatibility: Supports other Read locks and Access Locks



Access - When a user uses the LOCKING FOR ACCESS phrase. An Access lock allows users to  read a database object that is already under write-lock or read-lock. An access lock does not support Exclusive locks . An access lock does not ensure Data Integrity and may lead to "Stale Read"



Categorised on Levels, we can have locks at Database, Table or Row-level.



Row Hash Lock: A Row Hash lock is a 1-AMP operation where the Primary Index is utilized in the WHERE clause of the query. 


How it helps: Rather than locking the entire table, Teradata locks only those rows that have the same Row Hash value as generated in the WHERE clause.


Syntax: Locking Row for Access




Practical Example:


This is the situation faced today:

SQL1: Insert into FACT_CUST (col1,col2) Select(col1,col2) from WRK_CUST 


SQL2: Select * from FACT_CUST


Since, SQL1 was submitted first, there is a Write lock on FACT_CUST. SQL2 needs a Read Lock on FACT_CUST. So, it will wait until SQL1 is complete.



When inserting or updating rows from a query (Insert/ Select or Update where the primary index is not specified), a Write lock is placed on the table.


If you Insert Values or Update where PIVal = 12345 (via SQL or Load Utility) then a Write lock is placed at the row level.


If you do a select without specifying the PI values, a Read lock is placed at table level. A Read Lock will not read through an existing write lock (and vice versa) so the one who gets in second will be delayed or bounced if NOWAIT is specified.



If you put a LOCKING ROW (or Tablename) FOR ACCESS on the Select query, the Select will read through any Write lock at row or table level. (So-called "Dirty Read".) This only applies to Select - a Write lock cannot be downgraded to an Access Lock.


To overcome "Stale Read", we can allow read access through Views - put the LOCKING FOR ACCESS clause in the views.


Let me explain the strategy I use:

I have a view that is refreshed everyday.
View definition: 

replace view test_v
as
locking row for access
(
select * from test1
);

Now I load the delta rows into test2. Then once the processing completes and test2 table is ready, refresh the view as:

replace view test_v
as
locking row for access
(
select * from test2
);


Next step will be to move the contents of test2 into test1:

delete from test1 all;
insert into test1 select * from test2;

This will always give consistent data and very little downtime (required only during view refresh) 



Distinct and Group by in Teradata - What to use and when to use?


Distinct and Group-by are both used to identify unique rows in Teradata.

Generally for Large Dataset, Group-by is preferred. Why?


Group-by distributes the task of filtering unique rows to individual AMPs. So, the operation is AMP local is less CPU consuming. Look at the below explain steps to understand better.


  1) First, we lock a distinct PRD_DB."pseudo table" for
     read on a RowHash to prevent global deadlock for
     PRD_DB.FACT_SHC_DLY_OPR_INS.
  2) Next, we lock PRD_DB.FACT_SHC_DLY_OPR_INS for read.
  3) We do an all-AMPs SUM step to aggregate from a single partition of
     PRD_DB.FACT_SHC_DLY_OPR_INS
with a condition of (
     "PRD_DB.FACT_SHC_DLY_OPR_INS.DAY_NBR = DATE
     '2012-12-17'") with a residual condition of (
     "PRD_DB.FACT_SHC_DLY_OPR_INS.DAY_NBR = DATE
     '2012-12-17'") , grouping by field1 (
     PRD_DB.FACT_SHC_DLY_OPR_INS.VEND_PACK_ID
     ,PRD_DB.FACT_SHC_DLY_OPR_INS.LOCN_NBR).  Aggregate Intermediate
     Results are computed locally
, then placed in Spool 3.  The
     aggregate spool file will not be cached in memory.  The size of
     Spool 3 is estimated with high confidence to be 3,776,325,574 rows
     (241,684,836,736 bytes).  The estimated time for this step is
     49.05 seconds.


When we do the same operation using distinct, data is first copied to the spool and then the filter operation is performed.

Distinct is feasible for small dataset whereas Group-by is better for Large data set.

So, what happens when your data is skewed


In this scenario, distinct works faster, since the filter operation is done at Spool Level.
For group-by, the skewed AMP will take relatively longer time to complete its share of operation. And all the participating AMPs will have to wait for the skewed AMP. Hence the performance degrades and TotalAmpCPU increases.

Monday 17 December 2012

What results in increased CPU time - An overview

During a query, we may notice increase in the effective CPU usage or Total CPU Usage.

If we closely observe the DBQL tables, we will see the following points:

1. The queries with more complex calculations utilise more CPU
2. If there are more joins, it results in increased CPU
3. Calculation while inserting data will lead to more CPU.
     For example:

Insert into TEST
Select
Cus_id
,Max(Day_nbr) +1
,Amount
,Name
From
Customer;

This is because the operation will be done on spool. There is a overhead associated with each insert.

4. If the data is skewed, we will see the total CPU rise.

   This is because, in Teradata, each AMP performs its calculations independently.
Now,say an Aggregation is happening on each AMP. If one of the AMP is skewed, it will take more time to complete the operation. All the remaining AMPS will wait for this AMP.

This difference in TOTAL_AMP_CPU  and EFFECTIVE_CPU is referred to as Parallel Efficiency.

5. Use of SET tables lead to higher AMP CPU. It is because SET tables have a row level check on duplicates.


Teradata Inline Stats - How to over-ride the optimiser

Inline Stats are statistics collected in between a script, usually before a SELECT or a JOIN.

It is mostly used to over-ride the optimiser to pick a different PLAN.

For example:

We have a table FACT_SALES with PI on CUS_ID.

But, the Sales_Dimension has to be joined on PROD_ID, a primary key in the Dimension. There is no point joining based on CUS_ID, as it will lead to a spool-join rather than amp-local join.

So, we collect an Inline Stats by using:

Collects stats on Sales_Dim column PROD_ID;

This will help the optimizer to join the 2 tables based on PROD_ID.

Thursday 13 December 2012

Create Table as Select - Design Target table with same structure as Source

How to create a table as per the source table structure?

We may need in daily life, to create a table derived from source. This will make the downstream table automatically modified if any column in the Source is changed.

In order to create a table as per the source table structure, use the following SQL:

Example:

 CREATE TABLE ARP_TBLS_DB.FACT_SRS_DLY_WTS_ACT_DTL
(
LOCN_NBR ,
REG_NBR ,
TRS_NBR ,
DAY_NBR ,
PRD_IRL_NBR ,
ITM_PLA_CNT ,
ITM_ACP_CNT ,
ITM_REJ_CNT ,
ITM_NRSP_CNT ,
ITM_CNC_CNT ,
WTS_RVN_DLR ,
WTS_CST_DLR ,
FILL_TRY_ID ,
PRI_TRY_LOCN_NBR ,
DATASET_ID ,
CREAT_TS ,
MOD_TS
)
AS
(
SELECT
A.LOCN_NBR,
A.REG_NBR,
A.TRS_NBR,
A.DAY_NBR,
A.PRD_IRL_NBR,
A.ITM_PLA_CNT,
A.ITM_ACP_CNT,
A.ITM_REJ_CNT,
A.ITM_NRSP_CNT,
A.ITM_CNC_CNT,
A.WTS_RVN_DLR,
A.ITM_ACP_CNT * COALESCE((
CASE
WHEN A.UN_CST = 0 THEN 0
ELSE (
CASE
WHEN A.UN_CST IS NULL
AND A.WTS_RVN_DLR <> 0
AND A.ITM_ACP_CNT <> 0 THEN B4.NATL_CST_PRC
ELSE (
CASE
WHEN A.UN_CST IS NULL
AND A.WTS_RVN_DLR = 0
AND A.ITM_ACP_CNT <> 0 THEN 0
ELSE A.UN_CST
END)
END)
END), 0) AS WTS_CST_DLR,
A.FILL_TRY_ID,
A.PRI_TRY_LOCN_NBR,
A.DATASET_ID,
A.CREAT_TS,
A.MOD_TS
FROM
FACT_SRS_DLY_WTS_ACT_DTL_NEW1 A
LEFT OUTER JOIN ARP_VW_DB.LU_SRS_PRODUCT B4
ON A.PRD_IRL_NBR = B4.PRD_IRL_NBR
) WITH DATA
PRIMARY INDEX (LOCN_NBR, REG_NBR, TRS_NBR, PRD_IRL_NBR);

Teradata Journals - Maintains database consistency

What are the types of Teradata journals?

This is often asked in interviews. As it happened, the interviewer asked me


"How many journals are there in Teradata? How many have you used?"
Well, the answer is:


For the User = 1
For the System = 2


 Permanent journal - This journal is available to the user. It maintains complete history of the table
The journal is specified along with the DDL



For example:


CREATE TABLE TA_62917_QRMDB.VERSION_HEAD
,NO FALLBACK
,BEFORE JOURNAL
,DUAL AFTER JOURNAL
(
VERSION_ID INTEGER
, FLOWNAME VARCHAR(20)
,LOAD_DATE DATE FORMAT 'YYYY-MM-DD'
)
PRIMARY INDEX(VERSION_ID)
;


The following are the feature of Permanent Journals:


Continuously Active
Used for both tables or databases
Provides rollforward for hardware failure recovery
Provides rollback for software failure recovery
Provides full recovery of nonfallback tables
Reduces need for frequent, full-table archives





Down AMP recovery journal - This is available to the System automatically. This journal stores all entries of the down AMP. Once the AMP comes back online, it copies all the missed entries. Once the catch-up is completed, the DARJ ( also called the Recovery Journal) is dropped.

The following are the features of Recovery Journal:



It is active during an AMP failure only
It includes journals FALLBACK tables only
The journal is discarded after the down AMP recovers


Transient journal - This is also a System Journal. It maintains history of the current transaction.

Functions of the Transient journal are:



Logs BEFORE images for all transactions
Is used by system to roll back failed transactions aborted either by the user or by the system
Captures:


– BT/ET images for all transactions
– Before images for updates and deletes
– Row IDs for inserts
– Control records for creates and drops
-Rollback in case parallel queries failed


Tuesday 11 December 2012

Volatile Tables in Teradata - How and when to use volatile tables

Volatile Tables:


There are temporary session specific tables in Teradata. They are created in the spool-space of the user.

Advantages:

1. In query tuning, we use volatile tables to hold session-specific data
2. Large joins can be broken down into temporary volatile tables. But, if the join is an AMP-local join, no need to create a Volatile table


Key considerations:  Consider the performance benefits before creating volatile table.

Collect Statistics on Volatile table may help the optimiser understand the Bias and the data volume.

Example Query:


CREATE MULTISET VOLATILE TABLE WRK_FACT_SHC_DLY_OPR_INS
AS
(
SELECT
A.VEND_PACK_ID,
A.LOCN_NBR,
(A.DAY_NBR + 1) AS DAY_NBR,
A.MDS_STS,
A.RPL_TYP_CD,
A.INS_TYP_CD,
A.INS_SUB_TYP_CD,
A.TTL_UN_QT,
A.TTL_CST_DLR,
A.TTL_SLL_DLR,
A.PVE_UN_QT,
A.PVE_CST_DLR,
A.PVE_SLL_DLR
FROM
  $TD_TBLS_DB.FACT_SHC_DLY_OPR_INS A
WHERE  A.DAY_NBR = (SELECT DAY_NBR FROM MAX_DAY_NBR_CURR_DT)
group by VEND_PACK_ID,LOCN_NBR,DAY_NBR,MDS_STS,RPL_TYP_CD,INS_TYP_CD,INS_SUB_TYP_CD,TTL_UN_QT,TTL_CST_DLR,TTL_SLL_DLR,PVE_UN_QT,PVE_CST_DLR,PVE_SLL_DLR
) WITH DATA
PRIMARY INDEX (VEND_PACK_ID,DAY_NBR )
ON COMMIT PRESERVE ROWS;


Monday 10 December 2012

The Teradata ANSI Merge - Implementing UPSERT in Teradata

Update and Insert - The Concept


In SQL, we have the concept of UPDATE Else INSERT, which means:

If I have rows that are already in the table and their attributes changed, they should be updated.
Else, if I have new rows, insert them into the table.

Now, Teradata has a new feature since V12.0, known as the ANSI Merge.

Key benefits/Advantages:

1. ANSI Merge is more portable across platforms
2. ANSI Merge takes care of which rows to update and which ones to insert, based on the columns specified in the SET block of Update
3. ANSI Merge ignores rows that are not included in the SET column list.
4. ANSI Merge improves the performance of the queries. This is due to the fact that ANSI Merge is a single-table read, whereas traditional UPSERT is a 2 table-table read.
It uses a new step called MRM(Merge Row Multiple Operations) which does updates and inserts in a single step.

Disadvantages:

1. The MERGE Using Table(Derived table S) and the Target table (ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS) in the example, when joined, should return unique rows.
If duplicates, or multiple entries, qualify for UPDATE, quite logically, MERGE fails.


Example:


MERGE INTO ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS
USING  (
SELECT  VEND_PACK_OPR_ID,  LOCN_OPR_NBR,   DAY_NBR,  MDS_STS,  RPL_TYP_CD,  INS_TYP_CD,   INS_SUB_TYP_CD,
                  SUM(UN_QT) AS UN_QT, SUM(CST_DLR) AS CST_DLR, SUM(SLL_DLR) AS SLL_DLR
FROM STG_VIEWS_PRD.INCR_SHC_DLY_OPR_INS WHERE DAY_NBR <> '9999-09-09'
GROUP BY  VEND_PACK_OPR_ID,   LOCN_OPR_NBR,   DAY_NBR,  MDS_STS,   RPL_TYP_CD,  INS_TYP_CD,   INS_SUB_TYP_CD ) S
ON  ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS.VEND_PACK_ID=S.VEND_PACK_OPR_ID
AND ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS.LOCN_NBR=S.LOCN_OPR_NBR
AND ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS.INS_TYP_CD=S.INS_TYP_CD
AND ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS.INS_SUB_TYP_CD=S.INS_SUB_TYP_CD
AND ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS.DAY_NBR = S.DAY_NBR


WHEN MATCHED THEN UPDATE
SET
    RPL_TYP_CD = S.RPL_TYP_CD
 ,  TTL_UN_QT = S.UN_QT
 ,   TTL_CST_DLR = S.CST_DLR
 ,   TTL_SLL_DLR = S.SLL_DLR
 ,   PVE_UN_QT   = CASE WHEN S.UN_QT > 0 THEN S.UN_QT ELSE 0 END
 ,   PVE_CST_DLR = CASE WHEN S.UN_QT > 0 THEN S.CST_DLR ELSE 0 END
 ,   PVE_SLL_DLR = CASE WHEN S.UN_QT > 0 THEN S.SLL_DLR ELSE 0 END

WHEN NOT MATCHED THEN INSERT
VALUES (
    S.VEND_PACK_OPR_ID,
    S.LOCN_OPR_NBR,
    S.DAY_NBR,
    S.MDS_STS,
    S.RPL_TYP_CD,
    S.INS_TYP_CD,
    S.INS_SUB_TYP_CD,
    S.UN_QT,
    S.CST_DLR,
    S.SLL_DLR,
        CASE WHEN S.UN_QT > 0 THEN S.UN_QT ELSE 0 END,
        CASE WHEN S.UN_QT > 0 THEN S.CST_DLR ELSE 0 END,
        CASE WHEN S.UN_QT > 0 THEN S.SLL_DLR ELSE 0 END
);


Explain:

So, lets see what the explain plan says:

1) First, we lock a distinct ARP_TBLS_PRD."pseudo table" for
     write
on a RowHash to prevent global deadlock for
     ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS.
  2) Next, we lock STG_TBLS_PRD.INCR_SHC_DLY_OPR_INS for access,
     and we lock ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS for write.
  3) We do an all-AMPs SUM step to aggregate from
     STG_TBLS_PRD.INCR_SHC_DLY_OPR_INS by way of an all-rows scan
     with a condition of (
     "STG_TBLS_PRD.INCR_SHC_DLY_OPR_INS.DAY_NBR <> DATE
     '9999-09-09'") , grouping by field1 (
     STG_TBLS_PRD.INCR_SHC_DLY_OPR_INS.VEND_PACK_OPR_ID
     ,STG_TBLS_PRD.INCR_SHC_DLY_OPR_INS.LOCN_OPR_NBR
     ,STG_TBLS_PRD.INCR_SHC_DLY_OPR_INS.DAY_NBR
     ,STG_TBLS_PRD.INCR_SHC_DLY_OPR_INS.MDS_STS
     ,STG_TBLS_PRD.INCR_SHC_DLY_OPR_INS.RPL_TYP_CD
     ,STG_TBLS_PRD.INCR_SHC_DLY_OPR_INS.INS_TYP_CD
     ,STG_TBLS_PRD.INCR_SHC_DLY_OPR_INS.INS_SUB_TYP_CD).
     Aggregate Intermediate Results are computed locally, then placed
     in Spool 3.  The size of Spool 3 is estimated with no confidence
     to be 7,696,317 rows (692,668,530 bytes).  The estimated time for
     this step is 0.08 seconds.
  4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
     an all-rows scan into Spool 1 (used to materialize view, derived
     table or table function S) (all_amps) (compressed columns allowed),
     which is redistributed by the rowkey of (
     STG_TBLS_PRD.INCR_SHC_DLY_OPR_INS.VEND_PACK_OPR_ID,
     STG_TBLS_PRD.INCR_SHC_DLY_OPR_INS.DAY_NBR) to all AMPs.  Then
     we do a SORT to partition Spool 1 by rowkey.  The size of Spool 1
     is estimated with no confidence to be 7,696,317 rows (461,779,020
     bytes).  The estimated time for this step is 0.04 seconds. 



This the portion mentioned in Point 3. So only those columns listed in this condition are considered for Merge. Any other column, will be ignored by Merge.

How it helps?

If you have 50,000 rows, of which only 1000 rows changed. Merge will automatically update the 1000 rows and not touch the remaining 49,000 rows.
Try to make sure that the source and target tables have the same PI and PPI, which results in even faster operation.


  5) We do an all-AMPs merge with matched updates and unmatched inserts
     into ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS from Spool 1 (Last
     Use) with a condition of (
     "(ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS.VEND_PACK_ID =
     VEND_PACK_OPR_ID) AND
     ((ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS.LOCN_NBR =
     LOCN_OPR_NBR) AND
     ((ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS.INS_TYP_CD =
     INS_TYP_CD) AND
     ((ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS.INS_SUB_TYP_CD =
     INS_SUB_TYP_CD) AND
     (ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS.DAY_NBR = DAY_NBR ))))").
     The number of rows merged is estimated with no confidence to be
     7,696,317 rows.
  6) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.

Sunday 2 December 2012

Search for a pattern in a derived table - Teradata like operator and display Single-Quotes

We all know about Pattern-Matching and how it works.

But, in a real life scenario, we have to find matching data from 2 different applications.

The below query was used to compare 2 applications where the 'date' part (last 5 digits) of the Surrogate key differed.

Key take-aways:

1. Use of LIKE ANY keyword
2. How to use "Single-quotes" as Character in Teradata

Query:

SELECT * FROM DW_PROD2.DIM_CUSTOMER
WHERE CAST(CUS_ID_NO AS VARCHAR(20)) LIKE ANY
(
SELECT 
'''' || (SUBSTRING(CAST(A.CUS_ID_NO AS VARCHAR(20)) FROM 1 FOR 11 ) || '%' || ''''
FROM
DW_PROD1.CUSTOMER
WHERE TRS_DT = '2012-11-17'
);

Points to remember:

1. Converting CUS_ID_NO to Varchar was necessary since Pattern-Matching can be done only for Character values
2. The inner query will give results like