|
Replies:
13
-
Pages:
1
-
Last Post:
Nov 24, 2009 6:55 AM
Last Post By: user10390682
|
|
|
Posts:
82
Registered:
11/05/09
|
|
|
|
LKM SQL to MSSQL
Posted:
Nov 10, 2009 12:51 PM
|
|
|
This is my requirement
I need to create a new knowledge module based on LKM SQL to MSSQL that will build its work table based on the target table instead of the source in terms of the column definitions. The problem we are facing is that the current LKM uses the source table for the work table and this causes us problems with Unicode when connecting to the AS400's as the AS400 metadata shows char (or varchar) instead of nvarchar even when the data is unicode.
Can anyone explain me what the issue is and how to find a solution? |
|
|
Posts:
35
Registered:
09/16/09
|
|
|
|
Re: LKM SQL to MSSQL
Posted:
Nov 11, 2009 11:43 AM
in response to: user10390682
|
 |
Helpful |
|
|
|
My experience with AS 400 is limited, but I can add some general thoughts. ODI will automatically create a target column with the data type that represents the closest match to the data type of the source column. This column will be created on the staging/target area.
If there are issues loading the data, then you can play around with the model definitions for columns. A workaround would be to export the AS 400 data to a text file and then load the text file to MSSQL. If you don't have to many records, you can also try to use Sunopsis Memory Engine as your staging area.
Regards,
Matt
|
|
|
Posts:
82
Registered:
11/05/09
|
|
|
|
Re: LKM SQL to MSSQL
Posted:
Nov 13, 2009 6:34 AM
in response to: Matt_SJC
|
|
|
|
Thanks for taking interest in my problem.
I didn't get what you have said.
|
|
|
Posts:
1,609
Registered:
11/05/07
|
|
|
|
Re: LKM SQL to MSSQL
Posted:
Nov 13, 2009 6:39 AM
in response to: user10390682
|
|
Helpful |
|
|
|
Hi,
can you post the KM command please? I will try to make the necessary alter to work...
Cezar Santos
[www.odiexperts.com]
|
|
|
Posts:
82
Registered:
11/05/09
|
|
|
|
Re: LKM SQL to MSSQL
Posted:
Nov 18, 2009 11:02 AM
in response to: Cezar Santos - ...
|
|
|
|
What do you mean by KM command?
Are you asking me to give the commands on the source and target of the old LKM?
These are commands
Command : Truncate Table
Teacnology : MSSQL
Command on target : truncate table <%=snpRef.getTable("L","TARG_NAME","A")%>
There is no command in command on source
Command : Load Data
Technology : Undefined
Command on Source:
select <%=snpRef.getPop("DISTINCT_ROWS")%>
<%=snpRef.getColList("", "[EXPRESSION]\t[ALIAS_SEP] [CX_COL_NAME]", ",\n\t", "", "")%>
from <%=snpRef.getFrom()%>
where (1=1)
<%=snpRef.getFilter()%>
<%=snpRef.getJrnFilter()%>
<%=snpRef.getJoin()%>
<%=snpRef.getGrpBy()%>
<%=snpRef.getHaving()%>
Command on Target
Technology : MSSQL
insert into <%=snpRef.getTable("L","TARG_NAME","A")%>
(
<%=snpRef.getColList("", "[COL_NAME]", ",\n\t", "","")%>
)
values
(
<%=snpRef.getColList("", ":[CX_COL_NAME]", ",\n\t", "","")%>
)
Edited by: user10390682 on Nov 18, 2009 11:08 AM
|
|
|
Posts:
235
Registered:
06/05/06
|
|
|
|
Re: LKM SQL to MSSQL
Posted:
Nov 20, 2009 7:48 AM
in response to: user10390682
|
|
Helpful |
|
|
|
So, your problem is more about encoding issue.
I think that rather than making a modification to the KM, lets look at a different approach.
Open Topology Manager -> Physical Architecture
Select IBM DB2/400 (I assume that this is technology for AS400) and then expand DataTypes.
Double Click "VARCHAR" and select "Converted To" tab.
Goto MS SQL Server and change the DataType to NVARCHAR.
And this should start creating the intermediate tables using NVARCHAR
Hope that helps.
|
|
|
Posts:
82
Registered:
11/05/09
|
|
|
|
Re: LKM SQL to MSSQL
Posted:
Nov 20, 2009 12:39 PM
in response to: Ace2
|
|
|
|
My issue is not only converting VARCHAR to NVARCHAR but there are extra columns in target which are not in source. I want those extra columns also to be sent. So i have to write a LKM that will create work table on target instead of source.
|
|
|
Posts:
235
Registered:
06/05/06
|
|
|
|
Re: LKM SQL to MSSQL
Posted:
Nov 20, 2009 1:10 PM
in response to: user10390682
|
|
Helpful |
|
|
|
Please help me understand a little bit more.
If the columns are already in the target and not in the source, why do you need them in the intermediate tables ?
Where do you want to send the extra columns ?
What would the values in the extra columns look like ?
|
|
|
Posts:
1,609
Registered:
11/05/07
|
|
|
|
Re: LKM SQL to MSSQL
Posted:
Nov 23, 2009 2:45 AM
in response to: user10390682
|
|
Helpful |
|
|
Hi,
Did you get this one solved?
Cezar Santos
[www.odiexperts.com] |
|
|
Posts:
82
Registered:
11/05/09
|
|
|
|
Re: LKM SQL to MSSQL
Posted:
Nov 23, 2009 5:55 AM
in response to: Cezar Santos - ...
|
|
|
|
The interface where the LKM should be used is like temporary interface.
The table which is used in interface is same table EDIADTA but in two different schemas.
But EDIADTA in DB2400 has less columns and the other schema MSSQL has more columns, and the columns are totally different.
They are using a interface so that DB2400 columns canbe mapped and also to polulate other columns in MSSQL.
The table name is same in both source(DB2400) and target(MSSQL)
Please ask me if you have any more questions.
|
|
|
Posts:
235
Registered:
06/05/06
|
|
|
|
Re: LKM SQL to MSSQL
Posted:
Nov 23, 2009 8:22 AM
in response to: user10390682
|
|
|
I am sorry but I am getting confused. 
Would it be possible to share with us a diagrammatic representation of the proposed transformation along with column details in each table ?
"A picture is worth a thousand words ..."
This forum doesnt allow attachments, so maybe you can upload it to any free upload site and share the link with us. |
|
|
Posts:
82
Registered:
11/05/09
|
|
|
Posts:
235
Registered:
06/05/06
|
|
|
|
Re: LKM SQL to MSSQL
Posted:
Nov 24, 2009 6:17 AM
in response to: user10390682
|
|
|
|
Sorry,
but this site asks for login info, which I dont have.
Is it possible to upload to a publicly accessible server. Some members have been uploading to picasa
|
|
|
Posts:
82
Registered:
11/05/09
|
|
|
|
Re: LKM SQL to MSSQL
Posted:
Nov 24, 2009 6:55 AM
in response to: Ace2
|
|
|
|
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
