Friday, 20 October 2017

Cycle Handling in PostGreSQL

Below is the way to handle Cycle data in Postgresql database

 CREATE TABLE FOLDER(ID INT , NAME VARCHAR(255), PARENT INT);

INSERT INTO FOLDER VALUES(1, '/', null);
INSERT INTO FOLDER VALUES(2, 'src', 1);
INSERT INTO FOLDER VALUES(3, 'main', 2);
INSERT INTO FOLDER VALUES(4, 'org', 3);
INSERT INTO FOLDER VALUES(5, 'test', 2);


With RECURSIVE CTE(Id,NAME,Parent,Pathh,path, cycle)
as
(
Select Id,NAME,parent, cast(Id  as varchar(30)) pathh,ARRAY[Folder.Id] path,false from Folder
where parent is null
union all
Select 
F.id,F.name,F.Parent,cast(F.Id||'-->'||C.Pathh as varchar(30))  pathh,
C.Path||F.id path,F.id = any(path) as cycle
from 
Folder F,CTE C
where F.Parent=C.ID  and not cycle
)
Select C.* from CTE C
order by 1 


Friday, 13 October 2017

ORACLE PLSQL Exception Handling



SQL> create table a (x number (10), y number(10);
Table created.

SQL> insert into a values(1, 5);
SQL> insert into a values(2, 10);
SQL> insert into a values(3, 15);
SQL> insert into a values(3, 0);
SQL> insert into a values(4, 20);
SQL> insert into a values(5, 25);
SQL> commit;
SQL> select x, y from a order by x;

X Y
---------- ----------
1 5
2 10
3 15
3 0
4 20
5 25
SQL>
declare
v_temp number:= 30;
v_tot number;
v_x number;
v_y number;
cursor c_temp is
select x, y from a order by x;
begin
open c_temp;
loop
fetch c_temp into v_x, v_y;
exit when c_temp%notfound;
v_tot:=v_x+v_temp/v_y;
dbms_output.put_line('Total is :'||v_tot);
end loop;
close c_temp;
exception
when others then
dbms_output.put_line('Found Oracle error: '||SQLERRM);
end;
/

Total is :7
Total is :5
Total is :5
Found Oracle error: ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

Only Partial Records are Printed. 

Below Code Prints : Complete Numbers 

declare
  v_temp number:= 30;
  v_tot number;
  v_x number;
  v_y number;
  cursor c_temp is
    select x, y from a order by x;
begin
  open c_temp;
  loop
    fetch c_temp into v_x, v_y;
    exit when c_temp%notfound;
    begin
      v_tot:=v_x+v_temp/v_y;
      dbms_output.put_line('Total is :'||v_tot);
    exception
      when others then
      dbms_output.put_line('Found Oracle error: '||SQLERRM);
    end;
  end loop;
  close c_temp;
end;

Thursday, 12 October 2017

PLSQL / Works Cursors with Exception Handling

Simple Stored Procedure to Load the Cursor Dynamically:

create or replace procedure asf_cursor is 
TYPE EmpCurTyp  IS REF CURSOR;
c2  EmpCurTyp;
cursor C1 is (select 1 a from dual union all select 2 a from dual);
dynamic_query varchar2(1000);
i int;
var1 varchar(100);
BEGIN
open c1;
loop
fetch c1 into i;
EXIT WHEN c1%NOTFOUND;
dynamic_query:= 'select '''||'~'||i||'''  from dual';
open c2 for dynamic_query;
  LOOP
    FETCH c2 INTO var1;
    EXIT WHEN c2%NOTFOUND;
  END LOOP;
close c2;
DBMS_OUTPUT.PUT_LINE(var1);
end loop;
close c1;
END; 

set serveroutput on 
execute asf_cursor;

 

Exception Handling for Table Not Found:

-- set serveroutput on;
DECLARE 
TYPE EmpCurTyp  IS REF CURSOR;
c2  EmpCurTyp;
var1 varchar2(30);
drop_tab varchar2(100):='Select * from duall union all Select cast(2 as varchar(5)) from duall';
clob_out clob;
BEGIN
/*
open c2 for select * from duall;
LOOP
    FETCH c2 INTO var1;
    EXIT WHEN c2%NOTFOUND;
    dbms_output.put_line(var1);
    close c2;
  END LOOP;
  */
  open c2 for drop_tab;
  loop
  fetch c2 into var1;
   EXIT WHEN c2%NOTFOUND;
   clob_out:=clob_out||var1;
   end loop;
   close c2;
   dbms_output.put_line(clob_out);
  EXCEPTION
WHEN OTHERS THEN 
IF (SQLCODE = -942) THEN
DBMS_OUTPUT.PUT_LINE('Table Not Found');
clob_out:='~'||null;
dbms_output.put_line(clob_out);
END IF;
END;

Tuesday, 10 October 2017

Setting Different Java Version for Eclipse

  • Open eclipse config file eclipse.ini in your Eclipse folder.
  • Add -vm yourPath\Java\jre6\bin\javaw.exe like:

    -startup  plugins/org.eclipse.equinox.launcher_1.3.0.v20120522-1813.jar  --launcher.library  plugins/org.eclipse.equinox.launcher.win32.win32.x86_64_1.1.200.v20120522-1813  -product  org.eclipse.epp.package.java.product  --launcher.defaultAction  openFile  --launcher.XXMaxPermSize  256M  -vm   C:\Java\jre6\bin\javaw.exe  ...

Friday, 6 October 2017

NT Authentication In Talend

The solution is to place ntlmauth.dll in windows/system32 (or sysWow64) folder. but instead of placing it in system32 folder, we can place it in our own local folder and loaded by our talend jobs while establishing a connection to MS SQL Server.


Design time configuration:

1. Copy ntlmauth.dll to bin folder inside talend installed location. bin folder wont be present, create your own.

2. While executing the job, go to Advanced settings and select "Use Specific JVM arguments" and add the following additional argument

-Djava.library.path=./bin

3. Execute the talend job, it will use windows authentication

Inline image 1


Runtime configuration

1. While creating the package, ensure "bin" folder is also bundled along with ntlmauth.dll
2. Edit the .bat or .sh file and add the following entry

-Djava.library.path=./bin


Inline image 2

3. Execute the batch file, the job should run without any SSO error.

Tuesday, 3 October 2017

Talend Console logs to file

Redirecting Talend Console logs to file

Talend Open studio for data integration does not have any component to redirect console trace to a file. This blog post provides the solution for Redirecting Talend Console logs to file and is very easy to follow and extend. In previous article logging-using-talend we have seen how to customize Talend logs. This article shows how to redirect Talend console logs to file. In talend exchange, there is a custom component to perform similar job.

Solution



Redirecting Talend console logs is required by almost all Talend projects. This is very easy to implement in talend. Use tJava component at the start of any job and enter following lines into it.

Redirecting Talend Console logs

Redirecting Talend Console logs

java.io.File folder = new java.io.File("_logs/");
if (!folder.exists()) {
if (folder.mkdir()) {
System.out.println("Directory is created!");

}
else {
System.out.println("Failed to create directory!");
}
}
java.io.File logFile = new java.io.File("_log/"+projectName+"_.log");
java.io.PrintStream ts = new java.io.PrintStream(new java.io.FileOutputStream(logFile));
System.setOut(ts);
System.setErr(ts);

After executing the job, above code creates a flat file with "projectName.log" and starts writing data which appears on the console. You can create a new file at each execution by post/pre fixing with the time stamp in the file name or use following code. Attached image shows

java.io.File file = new java.io.File(projectName+"_"+TalendDate.getDate("CCYYMMDDhhmmss")+".log");

projectName is the variable name provided by the Talend and you can get the postfix date stamp by another variable TalendDate.getDate("CCYY-MM-DD hh:mm:ss"). Remove all the special characters in the time stamp. ProjectName can also be replaced by another global variable jobName.

After successfully executing this job, you can see ProjectName_TimeStamp.log  file is created in the root folder of Talend. This file contains complete console stack trace.

Drawback



 Drawback of implementing this strategy is that, it will not print or display anything on the console while using IDE or independent execution. This becomes difficult when developer is developing or debugging the job. Because, it is an extra effort for a developer to open a file every time to find out whether the job is executed successfully and generated error or not.

Monday, 2 October 2017

Talend Logging

java.io.File file = new java.io.File("C:/temp/myTalendLog.txt");
java.io.PrintStream ps = new java.io.PrintStream(new java.io.FileOutputStream(file));
System.setOut(ps);

Sunday, 24 September 2017

Upper Casing in char

char is a primitive type that represents a single 16 bit Unicode character while Character is a wrapper class that allows us to use char primitive concept in OOP-kind of way.

Example for char,

char ch = 'a';

Example of Character,

Character.toUpperCase(ch);

It converts 'a' to 'A'

Thursday, 17 August 2017

Query to Generate alter statement to disable and enable foreign key constraints in SQL Server


SELECT 
distinct
OBJECT_NAME(f.parent_object_id) Reference_TableName,
'Alter table '+OBJECT_NAME(f.parent_object_id)+' nocheck constraint '+ f.name ALter_Statement_Disable,
'Delete from '+OBJECT_NAME (f.referenced_object_id) Delete_Statment,
'Alter table '+OBJECT_NAME(f.parent_object_id)+' check constraint '+ f.name ALter_Statement_Enable
FROM 
   sys.foreign_keys AS f
INNER JOIN 
   sys.foreign_key_columns AS fc 
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE 
   OBJECT_NAME (f.referenced_object_id) = 'dashboard'

Sunday, 30 July 2017

Overriding Context Environments and Variables

1. Passing Context Environment :-

We can create different context group in the Talend Job and set the values of the context variables according to these groups. e.g. we can create a context group for Dev (Development environment) , Prod (Production environment) and provide different values to context variables based on the environment.

context_demo_run.bat --context=Prod

For using context group Dev defined in the Job:

H:\demo\context_demo> context_demo_run.bat --context=Dev

If we do not provide the context group then Job will automatically use the default context group defined in the Job.

For using default context group defined in the Job:

H:\demo\context_demo> context_demo_run.bat

2. Overriding Context Variables - We can also override the values of the individual context variables/parameters through command line. We can pass the argument --context_param followed by a name of the parameter defined in the Job and its value to override any of the parameters in the context. Use the following command to override the value of context parameter or variable.

H:\demo\context_demo> context_demo_run.bat --context_param <param-name>=<param-value>

For example: To override the value of context parameter DB_NAME to localhost

H:\demo\context_demo> context_demo_run.bat --context_param DB_NAME=localhost

Thursday, 18 May 2017

Export Model Repository Mappings using infacmd oie command

Export Command Without Control File: 

​./infacmd.sh oie ExportObjects -dn infav10_domain -un Admin -pd "@@@@@@" -sdn Native -pn Integration -rs mrs_irpo -fp /tmp/Mappings.xml -ow true

Export Command With Control File: 
./infacmd.sh oie ExportObjects -dn infav10_domain -un Admin -pd "@@@@@@" -sdn Native -pn Integration -rs mrs_irpo -fp /tmp/Mappings.xml -ow true -cp /tmp/ec_mapping.xml

Control File Samples

Control File :    All_Mappings In the Project Without considering Sub Folders

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<exportParams
<folders>
<folder recursive="false" >
<objectList type="Mapping" />
</folder>
</folders>
</exportParams>

Control File :    All_Mappings In the Project With consider Sub Folders contents

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<exportParams
<folders>
<folder recursive="true" >
<objectList type="Mapping" />
</folder>
</folders>
</exportParams>


Control File :  Export Particular Mapping 

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<exportParams
<folders>
<folder recursive="false" >
<objectList type="Mapping">
<object name="m_PassThrough_Mapping"/>
</objectList>
</folder>
</folders>
</exportParams>​

Control File : Export Particular Folder Mappings in a Project ​

​<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<exportParams
<folders>
<folder path="/SourceAdapter" recursive="false" >
<objectList type="Mapping" />
</folder>
</folders>
</exportParams>​

​ 

Wednesday, 17 May 2017

Operation not allowed for reason code “7” on table in DB2

This issue generally occurs when we altered/modified already existing table in IBM DB2.

We have to execute below Query when we get this error.

CALL sysproc.admin_cmd('REORG TABLE TABLE_NAME');

Below are the situations when we can ReOrg the table in DB2.

  • A high volume of insert, update, and delete activity on tables accessed by queries. If many rows are inserted, there may not be enough free space to keep them in the clustered sequence. If many rows are deleted, the table will still have the space allocated and a REORG can free the unnecessary space.
  • Significant changes in the performance of queries that use an index with a high cluster ratio. Some applications access groups of rows using the clustered index and may not perform well if the table becomes unclustered.
  • Executing RUNSTATS to refresh statistical information does not improve performance.
  •    In some cases, a RUNSTATS Utility can collect the current statistics and resolve performance problems. If a table has become unclustered or if it contains a large amount of free space, the REORG Utility may be needed to improve access efficiency.
  • The REORGCHK command indicates a need to reorganize your table.
  • If Row compression is being implemented for a table, the REORG Utility can be used to build the compression dictionary and compress the data rows.
Note:  alter table "APPS"."DEPARTMENTS" alter column DEPARTMENT_ID set not null; 

 


Wednesday, 10 May 2017

Creation of Informatica connections Using Infacmd

When a connection is created from command line, multiple options need to provide in -o option:


infacmd.sh createconnection
[ICMD_10023] Missing required command option: [<-DomainName|-dn>]
Usage:
<-DomainName|-dn> domain_name
<-UserName|-un> user_name
<-Password|-pd> password
[<-SecurityDomain|-sdn> security_domain]
[<-ResilienceTimeout|-re> timeout_period_in_seconds]
<-ConnectionName|-cn> connection_name
<-ConnectionId|-cid> connection_id
<-ConnectionType|-ct> connection_type
[<-ConnectionUserName|-cun> connection_user_name]
[<-ConnectionPassword|-cpd> connection_password]
<-Options|-o> options, separated by space in the form of name=value. Use single quote to escape any equal sign or space in the value.
Defines a connection and the connection options.




​./infacmd.sh createconnection -dn Domain_orcl -un Administrator -pd Lucid2016 -cn SQLServer -cid SQLServer -ct ODBC -cun ssasuser -cpd Lucid@123 -o "DataAccessConnectString=DW_SQLServer CodePage=UTF-8 EnableConnectionPool=true EnableQuotes=true QuoteChar=3 ODBCProvider='Microsoft SQL Server'"​

Monday, 8 May 2017

NLS_LANG Variable in Linux

Setting the NLS_LANG environment variable on the client to match that of the Oracle Database (found in v$nls_parameters system view) is NOT NECESSARILY the correct configuration.  NLS_LANG is used to tell the Oracle Client what character set the client's (or Informatica Server's) OS is using, so that appropriate code conversion can take place (or be skipped, when code pages match between Oracle client and server).  Please see Oracle Technical Note 158577.1 - "NLS_LANG Explained" for a more elaborate explanation as well as troubleshooting tips.  Please contact Oracle Support for configuration suggestions, as needed.

  • The NLS_LANG variable must to be in uppercase on UNIX.

  • If there is any mistake in the value setting of this variable (such as a spelling mistake), you may encounter the ORA-12705 error.
    For a list of valid languages, territories and character sets, the relevant National Language Support Guide for your version should be consulted.

  • Completely unsetting the NLS_LANG will use the default value AMERICAN_AMERICA.US7ASCII.
    This may fix the ORA-12705 error. However it may give unexpected results, as you are only dealing correctly with characters that can be represented completely with 7-bits. When you use a special characters (usually stored using the 8th bit), this will result in conversion problems.

  • On Windows, if there are several Oracle_HOMEs on the machine, ensure you have set the correct ORACLE_HOME
    Once you have determined the correct ORACLE_HOME , open the registry and verify the NLS_LANG entry in the special path.

  • On Windows, if you set NLS_LANG as an environment variable, this will take precedence over the value set in the Registry. 
    In most cases this is not preferable, so you may unset this variable.

  • On UNIX, depending on the shell, you will have to export the environment variable to make it visible to the sub-processes.

 

Oracle SQL Developer is the only "known-good" client side tool which can be used to view characters correctly and works independently of client-side NLS_LANG settings. 

Exporting a table that contains a CLOB column

​Problem Statement:​ 
What is the recommended way for exporting a table that contains a CLOB column to a SQL script? 
The standard feature of the SQL Developer only exports all columns that are not CLOBs. 


Solution:

Let us assume table named "Data" contains a clob type column. In order to get the insert statements for the content of the table ,
trigger the below query

SQL> select /*insert*/* from Data;


Note: Above sql should be executed in script mode .



Once the query executed , you can see the insert queries in the Script Output Area.

Sunday, 23 April 2017

Automated Script to Enable/Disable Foreign Key Constrain in Oracle Database

select 'ALTER TABLE HR.' || cons.TABLE_NAME || ' enable constraint ' || cons.constraint_name || ';' as, cons.owner as child_owner, cons.table_name as child_table, cons.constraint_name constaint_name, cons.constraint_type constraint_type, col.owner parent_owner, col.table_name parent_table, col.column_name column_name from dba_cons_columns col, dba_constraints cons where cons.r_owner = col.owner and cons.r_constraint_name = col.constraint_name and col.owner = 'HR

Thursday, 20 April 2017

Foreign Key Drop Statement SQL Server (Automated Script Generation of Foreign Key Constraint)

SELECT 'alter table '+OBJECT_NAME(f.parent_object_id) +' drop constraint '+ f.name AS Fk_Drop_Statement, 
   OBJECT_NAME(f.parent_object_id) AS TableName, 
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, 
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, 
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName 
FROM sys.foreign_keys AS f 
INNER JOIN sys.foreign_key_columns AS fc 
   ON f.OBJECT_ID = fc.constraint_object_id
ind of thinking we used when we created them - Albert Einstein"

Foreign Key Drop Statement SQL Server (Automated Script Generation of Foreign Key Constraint)

SELECT 'alter table '+OBJECT_NAME(f.parent_object_id) +' drop constraint '+ f.name AS Fk_Drop_Statement, 
   OBJECT_NAME(f.parent_object_id) AS TableName, 
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, 
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, 
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName 
FROM sys.foreign_keys AS f 
INNER JOIN sys.foreign_key_columns AS fc 
   ON f.OBJECT_ID = fc.constraint_object_id
ind of thinking we used when we created them - Albert Einstein"