-1

I have a remote mainframe db2 database for which I have created nicknames in my db2 server .

Problem is as below -

When I run query

SELECT * FROM LNICKNAME.TABLE - It runs and I can get all columns.

but if I run below query it never gives me any output and keeps running .

SELECT * FROM LNICKNAME.TABLE  a where a.columnB = 'ADH00040';

So technically it does not work if I add any where condition .

Bibek Panda
  • 519
  • 4
  • 10

2 Answers2

0

It doesn't seem like there is an error with your SELECT statement. So I am assuming that one of two things are happening:

Senario 1:

The file is really big and there isn't an index on columnB. If this is the case it would take long as the DB would have to read through each record and check if columnB = 'ADH00040'. To see how many records are in the table just run a count on the table

SELECT COUNT(*) FROM LNICKNAME.BMS_TABLE

Senario 2:

Something or someone is disconnecting your connection before your query is complete. I know you can limit the amount of CPU time a iSeries job is allowed before it gets ended forceably (CHGJOB CPUTIME(60000)). Is there no form of a job log that you could share with us?

Adrian Bannister
  • 523
  • 4
  • 11
  • Senario 1: I know file is big ..I mean it has more than a million record but if I connect to actual table than using nicknames am able to access it , so I do not think it will work. For Senario 2: --- If Something or someone is disconnecting then it wont allow me to run the select query also .. – Bibek Panda Jul 25 '16 at 17:23
  • @BibekanandaPanda Not necessarily true. A simple SELECT can easily run when WHERE clauses won't run. An unindexed WHERE clause may cause long-running temporary index builds that result in long delays. How long have you waited? – user2338816 Jul 26 '16 at 16:19
  • @user2338816 This query works with where when I try to access the remote database through sql tool like dbeaver ..but through nickname it wont work. – Bibek Panda Jul 28 '16 at 09:36
0

Are you sure your value is into your table?

try a like :

SELECT * FROM LNICKNAME.TABLE a where a.columnB like '%ADH00040%';

Esperento57
  • 16,521
  • 3
  • 39
  • 45