Friday, 24 March 2017

Updating Netezza With Dummy Values

update data set a = sub.DummyValue
from (
Select id,
'HH'||row_number() OVER(ORDER BY id ) DummyValue  from data a 
) sub
where data.id = sub.id;

Tuesday, 21 March 2017

Debugging Tools for ODBC Connection in Informatica


To test the ODBC connections, use the Informatica GCS tool SSGODBC.

 

From the Informatica Home (INFA_HOME) directory, do the following:

cd $INFA_HOME/tools/debugtools/ssgodbc
  1. Go to the directory corresponding to the Operating System.

    Example

    For Linux 64-bit, go to linux64.
  2. Run the ssgodbc file. 

    Usage: ssgodbc -d dsn -u username -p password [-v]

    -d Data Source Name (specified in odbc.ini file)
    -u ODBC username
    -p ODBC password
    -v verbose output

    Sample:

linux64.ssgodbc -d connectioninfo -u uname -p pword -v << ! > myfile.out 2>&1

./ssgodbc.linux64 -d CLAVEY -u admin -p password -v  Connected
ODBC version        = -03.52.0000-
DBMS name           = -NetezzaSQL-
DBMS version        = -04.05.0000 Release 4.5 (P-3) [Build 7835]-
Driver name         = -libnzodbc.so-
Driver version      = -Release 5.0.8 [Build 11925]-
Driver ODBC version = -03.51-

Enter SQL string:

Monday, 20 March 2017

Getting Primary Key Constraints in PostgreSQL

select tc.table_schema, tc.table_name, kc.column_name   from        information_schema.table_constraints tc,        information_schema.key_column_usage kc    where       tc.constraint_type = 'PRIMARY KEY'       and kc.table_name = tc.table_name and kc.table_schema = tc.table_schema      and kc.constraint_name = tc.constraint_name  order by 1, 2;

​ 

"We can't solve problems by using the same kind of thinking we used when we created them - Albert Einstein"

Thursday, 16 March 2017

PowerCenter session fails with the following error messages when running a session with SQL transformation:

Problem Description

PowerCenter session fails with the following error messages when running a session with SQL transformation:

ERROR : (1124 | TRANSF_1_1_1) : (IS | is_test) : pmsql_50018 : [ERROR] Failed to get the connections. 
ERROR : (1124 | TRANSF_1_1_1) : (IS | is_test) : pmsql_50009 : [ERROR] Static Connection information is inappropriate to create a valid Database Handle. 
ERROR : (1124 | TRANSF_1_1_1) : (IS | is_test) : CTSDK_43013 : Partition Driver level [SQL]: CT failed in init(). 
ERROR : (1124 | TRANSF_1_1_1) : (IS | is_test) : TM_6085 : The transformation [SQL] encountered fatal error. Terminating session.
Cause
This error occurs when SQL transformation is not set as Relational under Edit > Mapping tab.
Solution

To resolve this error do the following:

  1. Edit the Session Task Properties in the Workflow Manager.

  2. Click the Mapping tab.

  3. Select the SQL transformation under the Transformations folder.

  4. Edit the connection for SQL transformation.

  5. Switch None to Relational.

  6. Choose a valid Relational Connection from the list.

  7. Click Apply.

  8. Save the changes.

  9. Run the session.

Monday, 6 March 2017

IDQ MetaQueries


Select * from MODELREPO.PO_FOLDER where PSF_DISCRIMINATOR='MPROOTFOLDERMARKER' and PSF_EXTERNID='U:ViEFdKwEEeaI4dJFXcyuVg'
1637 MPROOTFOLDERMARKER 13 U:ViEFdKwEEeaI4dJFXcyuVg 0 419451
 
Select * from MODELREPO.PO_MPPROJECT where PSM_OPID=348 and rownum<=200

Select * from MODELREPO.PO_FOLDER where POF_NAME='Project_Folder' and  rownum<=500    "MODELREPO".PO_LOCKEDOBJECT    419457

Select * from "MODELREPO".PO_LOCKEDOBJECT where PSL_OPID=1030  419518

Select * from "MODELREPO".PO_PRINCIPAL  where PSP_OPID=1108

Select * from "MODELREPO".PO_CHANGELISTobJECT WHERE PSC_rsiD=110355 AND cast(poc_oldpath as varchar(200)) ='Profiling_Project/Project_Folder/0RelationalDataObject/DUPLICATE_CUST_TABLE'

Select * from "MODELREPO".PO_COLLECTIONENTRY_5  where PSC_OPID=24074 110355

Select * from "MODELREPO".PO_MAPPING  where PSM_OPID=1158

Select * from "MODELREPO".PO_EXECUTIONENVIRONMENT where PSE_RSID=419550 PSE_OPID=1504

Select * from "MODELREPO".PO_CHARACTERISTI2  where PSC_CONTAINER=419550

Select * from "MODELREPO".PO_PORT where PSP_CONTAINER=419550 PSP_OPID='41420'

Select * from "MODELREPO".PO_MAPPLETINTERFACECARDIN where PSM_CONTAINER=419550


Select * from "MODELREPO".PO_OUTLINELINK where PSO_CONTAINER=419550

Select * from "MODELREPO".PO_DATAHANDLER where PSD_CONTAINER=419550

Select * from "MODELREPO".PO_INSTANCE where PSI_CONTAINER=419550


Select * from "MODELREPO".PO_INSTANCE where POI_NAME='Mapplet_Input'


Select * from "MODELREPO".PO_DATAINTERFACE where PSD_CONTAINER=419550

Select * from "MODELREPO".PO_TYPEDELEMENT  where PST_CONTAINER=419550