When you receive an out of range (1 to 38) error while importing from one Oracle DB to another Oracle DB
- Go to your tMap
- Locate Big Decimals and any other precision numeric values.
- Be sure to fill in blanks.
- Save your job and re-run
Fin (I hope)
Situation Text to be written later.
UPDATE TABLE01 T1 SET (T1.FIELD01, T1.FIELD02) = (SELECT CROSS_FIELD_01, CROSS_FIELD02 FROM TABLE02 T2 WHERE T2.MATCHING_FIELD = T1.MATCHING_FIELD )
End of code text
Below are steps that you can take to import data from IBMs DB2 database into SQL Server. I’m using SQL Server 2012 Express edition.
Figuring this out has been one of the more frustrating tasks for me, so I hope this helps others like me.
Step 1.) Download and install DB2 Data Server Runtime Client (9.7 Fix Pack 4). Found in IBMs download page.
Step 2.) Extract the contents of your download to Program Files / IBM
Step 3.) Open your command prompt (as Administrator). Navigate to the “bin” folder under that folder you just copied into your directory.
Type db2oreg1 -i then type db2oreg1 -setup
Step 4.) Open up SQL Server Management Studio. Right click on the database that you want to import DB2 data into
Step 5.) Select the proper driver for DB2
Step 6.) Set the connection properties to your DB2 database
Step 7 .) Proceed with the wizard (Write a query to extract data or copy a table). I chose to write a query.
Step 8.) Preview mapping of query or table copy
Step 9). Success!
If you experience any problems and would like some help, please be as specific as possible with regards to the steps you took to get where you are.
Whether you have a character field holding time data (e.g. 10:45 PM) or you an actual time field (i.e. 10:45:00) and you want to find out the difference in minutes or hours between the two, you probably already know that you cannot use timestampdiff(), not without some modifications anyway.
In order to successfully extract the time difference, you need to convert your time into a timestamp. If the times we are comparing are in the same day, then you can use the current date. Example:
timestamp(cast(current date as varchar(10)) || '-' || cast(cast('10:50 PM' as time) as varchar(10)))
timestamp(cast(current date as varchar(10)) || '-' || cast(cast('10:50 AM' as time) as varchar(10)))
)) AS MINUTES
If you already have a date field with either an actual date only value or a character field with a date formatted YYYY-MM-DD, then you can replace “current date” with that date field value. Provided your field holding “time” properly casts into a time value, you can replace the string values (’10:50 PM’, ’10:50 AM’) with your time fields.
If your comparing the min and max of one or more fields, you can simply change the above to:
max(timestamp(cast(current date as varchar(10)) || '-' || cast(cast(tbl.field as time) as varchar(10))))
min(timestamp(cast(current date as varchar(10)) || '-' || cast(cast(tbl.field as time) as varchar(10))))
)) AS MINUTES
Comments and questions are welcome. 🙂
Unlike SQL Server, DB2 does not allow joins within its update statement. For example
UPDATE T1 SET T1.COL1 = 'NEW VALUE' FROM TABLE01 T1 INNER JOIN TABLE02 T2 ON T1.KEYCOL = T2.KEYCOL INNER JOIN TABLE03 T3 ON T2.KEY2COL = T3.KEYCOL WHERE T1.COL2 = 'SOME VALUE'
To accomplish the same thing in DB2 you would have to do the following:
UPDATE TABLE01 T SET T.COL = 'NEW VALUE' WHERE EXISTS ( SELECT * FROM TABLE01 T1 INNER JOIN TABLE02 T2 ON T1.KEYCOL = T2.KEYCOL INNER JOIN TABLE03 T3 ON T2.KEY2COL = T3.KEYCOL WHERE T1.COL2 = 'SOME VALUE' AND T.KEYCOL = T1.KEYCOL )
Essentially you write the query that would produce the rows that you want to update. Nest that query and execute a simple update statement with an EXISTS clause
If you are creating a BIRT report and you encounter the following error (org.apache.xml.serializer.ToXMLSAXHandler incompatible with org.apache.xml.serializer.SerializationHandler) in relation to a chart in your BIRT report, simply select the ouput format of your Chart to be PNG in BIRT (as opposed to SVG or any other image type that is causing rendering problems.
You are here because you are trying to generate a string using a SELECT statement in DB2, much like you would do in SQL SERVER. In other words, you want to execute a SELECT statement without the FROM clause in DB2 as you would in SQL SERVER. Below is how you do that.
In SQL Server, if you wanted to a string of text to be returned to you, you would simply type:
SELECT 'Whatever you want your string to say'
The results would be :
Whatever you want your string to say
In the results tab.
If you want to accomplish the same thing in DB2, instead of SELECT, use the word VALUES. So your statement would be typed like this:
VALUES 'Whatever you want your string to say'
The results will be the same