SQL Query in oracle pulling data from AS400, after 50 rows, oracle starts removing 1 character per column

gnivkor
gnivkor used Ask the Experts™
on
I have a sql query that pulls data from an as400

after 50 rows queried, the first digit of a column gets deleted

so lets say

in various columns on a row i have

423 5151 48930 2434

oracle actually reports

23 151 8930 434

the prior 50 rows do EXACTLY what i need them to do

based off of this query here

select 
  CM_PRIMRY_ROUTNO AS ROUTE, 
  CM_CUSTNO AS CUSTOMER_NUMBER,
  CM_CUSTMR_NAME AS NAME,
  CM_CUSTMR_ADDR_1 AS ADDRESS,
  CM_TKTMSG AS TICKET_MSG,
  ID_DESCRP AS DESCRIPTION,
  CM_PRIMRY_DLVSEQ_1,
  CM_PRIMRY_DLVSEQ_2,
  CM_PRIMRY_DLVSEQ_3,
  CM_PRIMRY_DLVSEQ_4,
  CM_PRIMRY_DLVSEQ_5,
  CM_PRIMRY_DLVSEQ_6,
  CM_NOWDUE AS BLNC, 
	CM_SLSMAN AS SALES_REP
FROM RAP003003.CUSMAS@"orcl.mycompany.com" INNER JOIN RAPFILES.IDCODEQRY@"orcl.mycompany.com" ON CUSMAS.CM_TKTMSG = IDCODEQRY.ID_FLDNUM  
WHERE CUSMAS.CM_PRIMRY_ROUTNO Not In (74,999) 
AND RAP003003.CUSMAS.CM_TKTMSG <> 4 
AND RAP003003.CUSMAS.CM_NOWDUE > =1000
AND RAPFILES.IDCODEQRY.ID_FLDLBL = 'TKTMSG'
AND RAP003003.CUSMAS.CM_BILCOD= 2
ORDER BY CUSMAS.CM_NOWDUE DESC;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
What 'tool' are you using to query the data?
Does it do any sort of paging?
Most Valuable Expert 2011
Top Expert 2012

Commented:
assuming you are using the odbc gateway, then check to see if the gateway is the problem or if the odbc driver is the problem.

use some other odbc tool, use the same DSN that the gateway uses and execute the query.

if you get the same results then the data or the odbc driver is the problem.
if you get the correct results then the data and odbc are fine and the gateway is messing up

or possibly it's how you are viewing the data and the results themselves are correct
to test this,  put the query in a pl/sql block to loop through and use dbms_output to display the results

Author

Commented:
i have ran the query through

Apex and sqldeveloper

when i query that same data from access it comes correct as it sits on the as400, when i query the data from isql, it comes correct as well
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Most Valuable Expert 2011
Top Expert 2012

Commented:
when you query through access and isql,  are you using the same dsn as the oracle gateway?

Author

Commented:
when i query from isql, i am querying from the same machine (linux) that oracle sits on using the same odbc drivers
Most Valuable Expert 2011
Top Expert 2012

Commented:
not just the same drivers

is it the same dsn?

Author

Commented:
yes
Most Valuable Expert 2011
Top Expert 2012

Commented:
if you established the data and dsn are correct, just keep going through the list I posted above.


it's either the gateway or the displaying method.

apex and sql*developer work reliably for me, but test them.

put the query in a pl/sql block, loop through it and use dbms_output.put_line to display the column value you're worried about.

if the output is correct then it's display method.  if the output is not correct then the gateway isn't returning the correct results.

in either case you'll have to go to oracle for support as apex, sql*developer and the odbc gateway are all from Oracle, but it'll be better to do the diagnostic before hand, otherwise you'll just have an extra layer of support to have to muddle through when you file the SR.

Author

Commented:
i do not have oracle SR. i will keep trying to trouble shoot, hopefully someone else will have some ideas

Author

Commented:
anyone else have any ideas, i have exhausted what i thought might be an issue
Most Valuable Expert 2011
Top Expert 2012

Commented:
have you tried everything suggested above?  if not, please do.  if so,  please post results of each i.e. don't simply say "yes",  tell us what happened for each trial

Author

Commented:
i installed and enabled the dbms_output package but am affraid i do not know how to proceed in relation to this query, i am researching to see if i can figure it out without having to open up another thread
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> i installed and enabled the dbms_output package

you shouldn't have needed to install that.  It should have already been in place but anyway, now that you have it.  What where the results of that trial?

There is no reason to open another thread as long as your pursuing the the suggestions introduced within this one.

Author

Commented:
well, i am not sure what it is that you are asking me to do by running the query in an pl/sql block and looping through it with dbms_output.put_line.

I do not know the correct syntax to achieve this and get the results you are asking for
Most Valuable Expert 2011
Top Expert 2012

Commented:
begin
   for x in (select .....           --- put your query here
    ) loop
         dbms_output.put_line(x.column_name);  
   end loop;
end;


change "column_name" to be the column you think has the wrong results, leave the "x." in place

Author

Commented:
using

begin
   for x in (select 
  CM_PRIMRY_ROUTNO AS ROUTE, 
  CM_CUSTNO AS CUSTOMER_NUMBER,
  CM_CUSTMR_NAME AS NAME,
  CM_CUSTMR_ADDR_1 AS ADDRESS,
  CM_TKTMSG AS TICKET_MSG,
  ID_DESCRP AS DESCRIPTION,
  CM_PRIMRY_DLVSEQ_1,
  CM_PRIMRY_DLVSEQ_2,
  CM_PRIMRY_DLVSEQ_3,
  CM_PRIMRY_DLVSEQ_4,
  CM_PRIMRY_DLVSEQ_5,
  CM_PRIMRY_DLVSEQ_6,
  CM_NOWDUE AS BLNC, 
	CM_SLSMAN AS SALES_REP
FROM RAP003003.CUSMAS@"orcl.mycompany.com" INNER JOIN RAPFILES.IDCODEQRY@"orcl.mycompany.com" ON CUSMAS.CM_TKTMSG = IDCODEQRY.ID_FLDNUM  
WHERE CUSMAS.CM_PRIMRY_ROUTNO Not In (74,999) 
AND RAP003003.CUSMAS.CM_TKTMSG <> 4 
AND RAP003003.CUSMAS.CM_NOWDUE > =1000
AND RAPFILES.IDCODEQRY.ID_FLDLBL = 'TKTMSG'
AND RAP003003.CUSMAS.CM_BILCOD= 2
ORDER BY CUSMAS.CM_NOWDUE DESC
    ) loop
         dbms_output.put_line(x.ROUTE);   
   end loop;
end;

Open in new window


the results were

anonymous block completed
Most Valuable Expert 2011
Top Expert 2012

Commented:
where is the dbms_output?

Author

Commented:
849
702
557
289
514
286
839
605
101
827
221
620
778
101
101
760
221
255
108
837
606
735
816
101
822
850
769
103
849
684
701
622
522
825
572
689
805
705
289
617
815
807
827
850
108
514
682
234
689
237
10
33
51
47
27
34
9
5
11
81
82
60
82
19
7
45
35
44
89
23
67
60
35
86
37
84
88
5
17
2
5
23
44
1
20
41
69
35
11
4
55
60
86
5
14
33
5
89
16

Open in new window



routes

10
33
51
47
27
34

should really be

810
633
651
647
827
234

Author

Commented:
this is happening with all columns that have digits on that row, other columns depend on these columns to return results.. those columns are blank because there is no route 10, if it were 810 as its suppose to be, i suspect that it would display the proper address and customer name
Most Valuable Expert 2011
Top Expert 2012
Commented:
thanks, unfortunately, if your data is correct then you have a problem in the gateway.

there's not really anything we can do about that, only Oracle can fix it.

I do wonder about the switch at 50.

What is the DSN's fetch buffer size set to?  If it's 50, maybe the problem is in secondary fetches

As a workaround, can you set it to something really large, Big enough that it could fetch the entire result set in one pass?  That might consume too much memory though.

Another option, set it to 1.  See if some internal operations might change if it's processing singletons.

If those work, it's not really a fix, but might be a viable workaround if/until you can get a working gateway from oracle.
Most Valuable Expert 2011
Top Expert 2012

Commented:
some other off-the-wall suggestions:

If we're in the realm of bugs, then "logical" solutions may not work, but we might be able to trick the gateway into letting the data come through.

  rearrange the columns

or fetch the route column twice

select CM_PRIMRY_ROUTNO AS dummy,   -- fetch it, but don't use it
 CM_PRIMRY_ROUTNO AS ROUTE,

or add a dummy column  

   select 'xxxxx' as dummy,



these, like the fetch idea above, aren't "fixes" they are merely attempts to "dodge" the bug by guessing at the symptoms that might cause it to surface.  While they might suffice as workarounds, you should document them heavily if you use them and be prepared to try other things as there is no guarantee they will be reliable in all circumstances.

Author

Commented:
HS_FDS_FETCH_ROWS = 1

is now returning the correct data, though it is not a practical solution it is getting the data issued correctly,

i will start to troubleshoot what is the ideal configuration parameter for the future

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial