Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    22

    Unanswered: SQL2000 linked server to DB2

    Ive got a linked server created in SQL2000 to DB2.

    Im using Client Access ODBC Drivers, and Microsoft OLE DB provider for ODBC Drivers.

    I have views created against the linked server as

    ALTER VIEW BLBATDT AS
    SELECT * FROM LURCH_PARADB.S102D4LM.PARADB.BLBATDT

    All seem(ED) to be working fine until I ran into this issue.

    Using the view
    If I select count(*) from BLBATDT returned value 786
    If I select * from BLBATDT I get 76 rows returned

    If I select from BLBATDT where field = value I get nothing, when in fact there is a value I specified.

    I modified the view creation to

    Create view as
    select * from openquery(LURCH_PARADB,
    'select *
    from S102D4LM.PARADB.BLHDR')

    Performed the above simple selects, (same results)

    I modified the view creation to
    Create view as
    select * from openquery(LURCH_PARADB,
    'select field_list_of_all_fields_in_the_table
    from S102D4LM.PARADB.BLHDR')

    Same results

    However if I modified the view creation to
    Create view as
    select * from openquery(LURCH_PARADB,
    'select field1, field2
    from S102D4LM.PARADB.BLHDR')

    And limit the fields selected to a small subset, everything works fine.

    I think the issue may be that the parent tables are very wide in total byte count and the number of fields is also large.
    This particular tables has 120 fields with a total record length of over a 1000 bytes.

    If this is the case, how do I resolve this?


    Has anyone ran into this before?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you post the syntax for the link?

    Is it OS/390 or AS/400?

    AND don't use SELECT *
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    The value returned by Count(*) with no where clause is a result of updated statistics, or lack thereof. Update your DB2 statistics.
    Fred Prose

  4. #4
    Join Date
    Jan 2004
    Posts
    22
    I initially created the linked server thru the add linked server.


    sp_addlinkedserver
    @server = 'LURCH_LINKED',
    @srvproduct = 'Microsoft OLE DB Provider for ODBC Drivers',
    @provider = 'MSDASQL',
    @datasrc = 'LURCH_PARADB'

    I realize select * is bad, and I'm only using it during testing.

    Interesting enough, if I create the view with

    Limited field list
    CREATE VIEW blhdr_test
    AS
    select * from openquery(LURCH_PARADB,
    'select BLBATID, INVNUM, GRSAMT, NETAMT
    from S102D4LM.PARADB.BLHDR')

    select count(*) from blhdr_test (13797 returned) INCORRECT

    All fields
    CREATE VIEW BLHDR_TEST1
    AS
    select * from openquery(LURCH_PARADB,
    'select *
    from S102D4LM.PARADB.BLHDR')


    select count(*) from blhdr_test1 (232) returned INCORRECT

    CREATE VIEW BLHDR
    AS
    SELECT * FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR

    select count(*) from blhdr_test1 (35415) returned CORRECT

    If I run
    select BLBATID
    from blhdr
    where blbatid = 788 (which is a valid number)

    0 rows returned

    If I run

    select '',BLBATID
    from blhdr
    where blbatid = 788 (which is a valid number)

    3 Rows returned


    I installed SP3 on my SQL2000 server, hoping that would resolve this issue, but it did not.

    Any help would be appreciated.

  5. #5
    Join Date
    Jan 2004
    Posts
    22
    Originally posted by fprose
    The value returned by Count(*) with no where clause is a result of updated statistics, or lack thereof. Update your DB2 statistics.
    Select count only returns incorrect results when ran against the linked server I have setup.

  6. #6
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    I agree, the linked server is the issue. Update the statistics on DB2!
    Fred Prose

  7. #7
    Join Date
    Jan 2004
    Posts
    22
    It's a much bigger issue than select count.

    Example

    select blbatid, invnum
    from blhdr
    where blbatid = 788

    Returns 0 rows.

    while the same select against the parent table (NOT THRU the linked server) returns 3 rows.

    I've setup a linked server using IBM OLE DB FOR AS400, it works, even though I can't see the tables listed under linked servers, but it's terribly slow.

    I'm thinking of trying HIS drivers. But if I remember correctly in my reading, HIS requires some packages to be ran against the server and I couldn't get HIS to work before.

  8. #8
    Join Date
    Aug 2004
    Posts
    3

    I'm having similar problems

    I get a similar problem in that I get a subset of data when doing

    Select BGC7CD, BGCZTX from Mapics_TEST.SCOTT.AMFLIBF.MBBGREP
    or
    Select * from Mapics_TEST.SCOTT.AMFLIBF.MBBGREP

    But when I do a

    Select count(*) from Mapics_TEST.SCOTT.AMFLIBF.MBBGREP A I get the correct number of records.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •