 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Focal Point 
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Help - SQLServer to Oracle - DATETIME
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Help - SQLServer to Oracle - DATETIME Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] Help - SQLServer to Oracle - DATETIME | Login/Join |
| Member |
Hi, May be a simple question, pardon me  I am an iWay person but recently started using DM effectively.My requirement is to copy data from SQLServer to Oracle 10g. I have successfully created the transformations, data flow and pflow. Even tested it just fine. Then came a situation where few of the oracle column was changed to timestamp. Thus, my inserts as varchar has to be changed. Shouldn't be a problem right. Did the following: (1) SQLServer source transformations - All these datetime value columns were defined as HYYMDS (2) Oracle target transformations - All these datetime value columns were defined as HYYMDS (3) Checked MAS files to be sure of these defintions. When I do a test transformations in the target I get correct values. Was so happy. Now the last part of running the data flow and checking them. sob.sob.sob. I get (FOC1383) UNSUPPORTED DATETIME FORMAT FOR FIELD : IDOC_INS_DATE What am I doing wrong. Please help !!! Thanks much in advance This message has been edited. Last edited by: Kerry, | ||
|
| Member |
Forgot to mention Oracle datatypes are TIMESTAMP and the source SQLServer datatypes for these columns are DATETIME | |||
|
| Virtuoso |
Were the master files for both tables created using the synonym generator? Are you actually performing source and/or target transformations on the date data or are you simply extracting from the SQLServer table and loading into the Oracle table? If you are performing transformations on the dates, what kinds of changes are you making? You might open the flow and then switch to Text View to see if anything in the code stands out as a potential problem. You should look closely at any formats in the code that are being used for processing the date columns. (For example, with the Text View open do a search on 'IDOC_INS_DATE'.) WebFOCUS 7.7.05 | |||
|
| Guru |
While date columns are pretty standard, date and time columns are one of those areas where every database is different, in this case with the precision of the time portion. The MS SQL Server DATETIME column should be described as HYYMDs. Note the small "s" which indicates a value to the millisecond like '2010/08/13 00:00:00.000'. The newer ORACLE TIMESTAMP column should be described as HYYMDm. The small "m" is for microseconds like ...000000'. Once the columns are described accurately your data flow will work fine. N/A | |||
|
| Member |
Dan, Yes I created using the synonym generator. I am not doing any transformations on these date columns. They should be direct mappings. I will look into the formats as you had mentioned. Thank You. Clif, Thank You. I will try with these formats and get back | |||
|
| Member |
I was off Friday Tried the new formats you had advised. SOURCE ------ FILENAME=OBIDOCDETAILS, SUFFIX=SQLMSS , $ SEGMENT=OBIDOCDETAILS, SEGTYPE=S0, $ FIELDNAME=IDOCNUMBER, ALIAS=IDocNumber, USAGE=A50V, ACTUAL=A50V, $ FIELDNAME=IDOC_INS_DATE, ALIAS=IDoc_Ins_Date, USAGE=HYYMD-s, ACTUAL=HYYMD-s, $ TARGET ------ FILENAME=ORAOBIDOCDETAILS, SUFFIX=SQLJDBC , $ SEGMENT=ORAOBIDOCDETAILS, SEGTYPE=S0, $ FIELDNAME=IDOCNUMBER, ALIAS=IDOCNUMBER, USAGE=A50V, ACTUAL=A50V, $ FIELDNAME=IDOC_INS_DATE, ALIAS=IDOC_INS_DATE, USAGE=HYYMDm, ACTUAL=HYYMDm, $ I am using SQLServer Adapter and Oracle JDBC Generic Adapter.. | |||
|
| Expert |
Nobody suggested a dash: HYYMDs Tom Flynn WebFOCUS 8.1.05 - PROD/QA DB2 - AS400 - Mainframe | |||
|
| Member |
oops, when I choose HYYMD (with milliseconds), thats what gets written.. I think I need to go and manually change then  Will make the changes now and try it. Thanks !!! | |||
|
| Member |
I changed it to FILENAME=OBIDOCDETAILS, SUFFIX=SQLMSS , $ SEGMENT=OBIDOCDETAILS, SEGTYPE=S0, $ FIELDNAME=IDOCNUMBER, ALIAS=IDocNumber, USAGE=A50V, ACTUAL=A50V, $ FIELDNAME=IDOC_INS_DATE, ALIAS=IDoc_Ins_Date, USAGE=HYYMDs, ACTUAL=HYYMDs, $ Now its HYYMDs to HYYMDm and I still get unsupported exception. | |||
|
| Expert |
In my humble opinion you shouldn't have to modify a Master generated by the synonym generator. I would leave the Masters untouched. Since the FOC1383 error is on a fieldname that is on both the source and target table, do you know which table the error occurs on? It's most likely the target table and most likely because of the "JDBC generic adapter. Have you tried using the adapter for Oracle? I did a quick search at Tech Support and here's one result: Case: 20162041 - TROUBLESHOOTING - HYYMDS fields invalid by JDBC Informix Symptom: (FOC1383) UNSUPPORTED DATETIME FORMAT FOR FIELD Problem: Using JDBC adapter configured to go against Informix database, an error message is received when trying to query against a master file that has: FIELD=REQUEST_TIME ,request_time, HYYMDS ,HYYMDS ,MISSING=ON ,$Solution: JDBC access to Informix is not supported due to the fact that Informix has a native interface. Francis  Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Member |
Partial Good news Restarted DM with HYYMDS values for both source and target and it works perfectly fine Yaay !!! Thanks everyone... Is there any special settings to handle NULL mapping to timestamp? If there is no source value for these columns, I get a garbage value - 7391-01-14 06:30:16 instead of null in target? | |||
|
| Virtuoso |
If you haven't already done so, specify your source date with MISSING=ON. If that doesn't seem to do the trick, then try adding SET NULL=ON to the top of your program. EDIT: Your target date should be specified with MISSING=ON, too.This message has been edited. Last edited by: Dan Satchell, WebFOCUS 7.7.05 | |||
|
| Member |
Thanks Dan.. I do have MISSING in the source. Can you please explain what u mean by changing the program? Do u mean the data flow content? Please let me know. Thanks much. FIELDNAME=IB_ACK_DATE, ALIAS=IB_ACK_Date, USAGE=HYYMDs, ACTUAL=HYYMDs, MISSING=ON, $ | |||
|
| Virtuoso |
Make sure MISSING=ON is also specified for the target date and test your flow before doing the following. If you look at the process flow in the Process Flow tab, are there any procedures being executed prior to the Data Flow? If so, then simply add SET NULL=ON to the procedure. If not, then create a new procedure (not a new flow) and place the command SET NULL=ON in it. Drag the procedure into your Process Flow and use the right mouse button to connect it between the Start button and the Data Flow. WebFOCUS 7.7.05 | |||
|
| Guru |
I read that your target database was ORACLE but missed that you were using the generic JDBC adapter. As with Informix, when there is a database specific adapter available we recommend that you use it to provide better coverage of that database features. N/A | |||
|
| Expert |
Yup, what I suggested. Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Help - SQLServer to Oracle - DATETIME
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Help - SQLServer to Oracle - DATETIMECopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |