none
How can a SUBSTRING function be used in a Linked Server?

    Question

  • Hi All,

    I have a DB2 Linked Server setup from my SQL Server 2000 Server. I am trying to load the contents of a table from DB2 to SQL Server. However, I had an issue with the SUBSTRING in the OPENQUERY.

    What is the equivalent function for SUBSTRING in DB2?

    DB2 Table Structure:

    ID INT,

    DT_TIME TIMESTAMP

    SQL Server Table Structure:

    ID INT,

    T_STAMP CHAR(10)

    The Data in DB2 looks something like:

    1  2012-01-01 09:33:39.000

    2 1900-01-01 09:33:39.000

    Now, I want only the TIME part to be loaded into SQL Server.

    I am using the following query and it doesn't work:

    INSERT INTO SQLTable
    SELECT * FROM OPENQUERY(DB2LS, 'SELECT SUBSTRING(DT_TIME, 12,10) FROM DSchema.TRP')

    Can someone help me with this?

    Thanks


    Known is a DROP, Unknown is an OCEAN.

    Thursday, May 09, 2013 7:54 PM

Answers

  • However I would not do this the way you are trying.  A DB2 timestamp is similar to a DATETIME2(7) data type.  Use that in SQL Server, then use CONVERT in TSQL to convert it to the format you want. 

    Tom, that will not work for Bangaaram, as he is on SQL 2000.

    The query that might work is

     SELECT CAST(DT_TIME AS char(10)) FROM DSchema.TRP

    If, by chance, the format when you cast date like this in DB2 is YYYY-MM-DD. If that does not work out, Bangaraam will need to read the DB2 manuals.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 09, 2013 10:00 PM

All replies

  • I assume you are getting an error?  What is that error?  I expect you are getting an error trying to run SUBSTRING on a TIMESTAMP data type.

    OPENQUERY passes the query directly to the DB2 server, so you need to write a DB2 query, not a TSQL query.

    However I would not do this the way you are trying.  A DB2 timestamp is similar to a DATETIME2(7) data type.  Use that in SQL Server, then use CONVERT in TSQL to convert it to the format you want. 

    Thursday, May 09, 2013 8:52 PM
  • However I would not do this the way you are trying.  A DB2 timestamp is similar to a DATETIME2(7) data type.  Use that in SQL Server, then use CONVERT in TSQL to convert it to the format you want. 

    Tom, that will not work for Bangaaram, as he is on SQL 2000.

    The query that might work is

     SELECT CAST(DT_TIME AS char(10)) FROM DSchema.TRP

    If, by chance, the format when you cast date like this in DB2 is YYYY-MM-DD. If that does not work out, Bangaraam will need to read the DB2 manuals.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 09, 2013 10:00 PM
  • Hi Erland, I have a question here. Using the linked server, Data conversion thorugh the linked server might affect the performance of query right?

    If thats the case, it would be better having the conversions in the local machine right? Please correct me if am wrong.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, May 10, 2013 6:53 AM
  • Hi Erland, I have a question here. Using the linked server, Data conversion thorugh the linked server might affect the performance of query right?

    If thats the case, it would be better having the conversions in the local machine right? Please correct me if am wrong.

    I'm not sure that I understand the question, but I don't think it matters much as such for performance whether you convert in DB2 or in SQL Server. It could matter for query plans, but in that case it would be better to convert remotely, at least for the SQL Server optimizer.

    In this particular case, it may be a matter of survival. If Bangaraam would try to get the raw data from DB2 to his server, SQL Server may puke over the high precision, or dates that are outside the range of the datetime data type in SQL Server. So conversion should definitely occur in DB2.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 10, 2013 10:36 AM
  • Hi,

    Can't you use VARCHAR_FORMAT function something like VARCHAR_FORMAT(DT_TIME,'MI')  which returns minutes and you can do the same for seconds and milli seconds and concatenate?

    Regards,

    Brindha.

    Friday, May 10, 2013 1:42 PM
  • However I would not do this the way you are trying.  A DB2 timestamp is similar to a DATETIME2(7) data type.  Use that in SQL Server, then use CONVERT in TSQL to convert it to the format you want. 

    Tom, that will not work for Bangaaram, as he is on SQL 2000.

    The query that might work is

     SELECT CAST(DT_TIME AS char(10)) FROM DSchema.TRP

    If, by chance, the format when you cast date like this in DB2 is YYYY-MM-DD. If that does not work out, Bangaraam will need to read the DB2 manuals.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Awesome, your query seems to be working. I was wondering when you do a CAST of char(10), it usually picks up the date part (at least few of my other queries were only pulling the date part when I do a CAST of Char(10)). So, I doubted this doesn't work to get only the time part.

    But, there are few queries where I do a CAST of Char(30) for the same TIMESTAMP column. In such a case, the result seems to be something strange. Do you know why could that happen in such a way?

    Instead of getting the result set in the form of "2012-10-01 09:33:10.258", its returning "2012-10-01-09.33.10.258" replacing all the ':'  with '.' and also a '-' being returned between the date and time part.

    The same thing happened with the query you mentioned too. Instead of returning "17:53:11.000" from the source "1900-01-01 17:53:11.000", its returning "17.53.11" eliminating the fractions and replacing ':' with '.'


    Known is a DROP, Unknown is an OCEAN.

    Friday, May 10, 2013 1:47 PM
  • But, there are few queries where I do a CAST of Char(30) for the same TIMESTAMP column. In such a case, the result seems to be something strange. Do you know why could that happen in such a way?

    No, I'm not a DB2 guy. Remember, you are in a different territory, where the rules are not the same as in SQL Server. The suggestion CAST(DT_TIME AS char(10)) was just a wild guess; on SQL Server you would get something else for the datetime data type. (Datetime2 on SQL 2008 gives you YYYY-MM-DD.)

    If you have questions on DB2, you need to find a DB2 forum.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 10, 2013 6:26 PM
  • Anybody? Any thoughts about my last question?

    Known is a DROP, Unknown is an OCEAN.

    Thursday, May 16, 2013 6:16 PM
  • I would suggest using the DB2 TIME() function instead.  Please see:

    http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html

    Thursday, May 16, 2013 7:01 PM
  • I would do the conversion on the SQL side first then build your DB2 SQL statement as a string with your DB2 compliant variable(s). The tick marks and double quotes are always fun to monkey with before morning coffee.

    john alverson


    • Edited by J Alverson Thursday, May 16, 2013 11:17 PM
    Thursday, May 16, 2013 11:17 PM