When enable db2util missing show members of lib/file

[eduardomarco]

Describe the bug When enable db2util missing show members of lib/file

To Reproduce Steps to reproduce the behavior:

1. Go to 'IBM i'
2. Click on 'Member Browser'
3. Scroll down to 'lib/file'
4. Don´t show members

Expected behavior Show members on lib/file

Screenshots

Environment (please complete the following information):

• Extension version [0.5.2]
• IBM i OS version [e.g. 7.4]

Additional context Add any other context about the problem here.

[worksofliam]

Do you get any error messages?

Are you able to run this statement in Run SQL Scripts as the same user you're connecting with?

SELECT ( avgrowsize - 12 ) AS MBMXRL,
       iasp_number         AS MBASP,
       system_table_member AS MBNAME,
       source_type         AS MBSEU2,
       partition_text      AS MBMTXT
FROM   qsys2.syspartitionstat,
       qsys2.sysschemas
WHERE  table_schema = '${lib}'
       AND table_name = '${spf}'
       AND schema_name = system_table_schema 

[eduardomarco]

SELECT ( avgrowsize - 12 ) AS MBMXRL, iasp_number AS MBASP, system_table_member AS MBNAME, source_type AS MBSEU2, partition_text AS MBMTXT FROM qsys2.syspartitionstat, qsys2.sysschemas WHERE table_schema = '${EAMARCO}' AND table_name = '${CONTAS}' AND schema_name = system_table_schema

Don´t show records.

[worksofliam]

You need to try without the curly brackets. E.g.

WHERE table_schema = 'EAMARCO'
AND table_name = 'CONTAS'

[eduardomarco]

S

[worksofliam]

@eduardomarco What version of db2util are you using?

[eduardomarco]

db2util 1.0.11-dev

[worksofliam]

@eduardomarco Does QSYSINC/H open? Or do they both show nothing?

The work around is to disable SQL from the client.

[eduardomarco]

Both of them

[worksofliam]

I don't have a lot of time to look into this issue right now. Weird that you can execute the statement through Run SQL Scripts but not through db2util.

My recommendation, for the time being, is to disable SQL in the settings.

[eduardomarco]

I made that, Thanks

[worksofliam]

From an ssh shell, you could run this and see what happens:

DB2UTIL_JSON_CONTAINER=array /QOpenSys/pkgs/bin/db2util -o json "SELECT ( avgrowsize - 12 ) AS MBMXRL, iasp_number AS MBASP, system_table_member AS MBNAME, source_type AS MBSEU2, partition_text AS MBMTXT FROM qsys2.syspartitionstat, qsys2.sysschemas WHERE table_schema = 'EAMARCO' AND table_name = 'CONTAS' AND schema_name = system_table_schema"

[szsascha]

I've found it. Was a little night challenge before sleep :D

You can get behind the problem with the following steps:

1. run it in ssh, qshell and strsql on i. You will see that it's this only occurs in qsh environment
2. check file description of qsys2.sysschemas with WRKF and 8
3. copy SQL view create statement and run it in strsql and qsh. You see again that it's only working in strsql
4. If you know a bit about IBM i and QSH you'll recognize this line: QSYS2.OBJECT_STATISTICS('QSYS ','LIB ', NULL (IBM i based strictly on fieldlength, QSH not because it's UNIX)
5. Remove blanks and run it again
6. Then it's working also in QSH

But I think we've just found a IBM i internal bug here. They are pretty rare since it's such a stable system.

Unfortunately I can't implement a workaround in the VSCode extension because I have to get up early. Maybe I'll fix it on the weekend if someone else didn't fix this until then. :)

[worksofliam]

@szsascha I am curious to see what you share! Thanks for that information!!

[SJLennon]

@worksofliam @szsascha FWIW: I'm using pub400.com and it has db2util 1.0.11-dev. I have the same problem. If "enableSQL": false then you get no Database Browser; It true, you get no Member Browser. This is came up in #105.

(I know you asked for it to be updated on pub400 and Holger said he did. Not sure where he got the -dev version but it doesn't work. I asked on the pub400 user forums to load the latest stable version,which was 1.0.11, but it's a free machine and real work takes precedence and it's not impacting me any. And I suppose it is a handy test machine...)

[worksofliam]

For the record, I have the -dev version and it works on the system I use.

[SJLennon]

Curious indeed 😦 . Maybe it's an obscure language/CCSID issue. Do you have a pub400 account you could try? Again, it's not urgent for me. @worksofliam

[szsascha]

A little update from my side:

My plan was to extract the SQLs from the QSYS2.SYSPARTITIONSTAT and QSYS2.SYSSCHEMAS views's and implement them as an WITH clause into the SQL but with the bugs fixed. But there are 2 things why this dosn't work:

1. The SQL would get too large so it's difficult to read
2. I get some permission problems with SYSPARTITIONSTAT's SQL

Now I asked myself the question how the whole thing benefit's from using db2util. From what I hear db2util looks to be pretty unstable and has to be installed on the server.

So why we are using this @worksofliam? I thing all functionalities can be done in more stable ways.

BTW: I've tested it also on Holger's PUB400.

[worksofliam]

@szsascha the reason we are using db2util is because it's a quick and portable way of running SQL.

While I do agree it is largely not great, it is the easiest way of running statements with the type of connection we have and get back the result set as JSON.

I'd ideally like to figure out why this is happening instead of changing the db2util dependant.

[szsascha]

@worksofliam At the moment I see 2 options to get it running:

1. We wait (and hope) that IBM will fix it soon. But I think it's more a problem of the QSH/AIX environment and not db2util specific.
2. We try to create a workaround to ensure compatibility

In my opinion the second option would be the best.

I understand that you want to use the easy way with db2util. But I also think that now isn't the time for it. Maybe in a few years or month. Isn't there even a fallback mechanism in the extension? Why not create a library that makes it just as easy to use without depending on db2util?

[worksofliam]

Number 2 is the better option for sure. This is definitely a fixable problem.. we just have to figure out how to recreate it (I need to sign in to pub400).

The reason I am avoiding writing my own SQL tools is because I don't have the time to maintain it.

Code for IBM i is already large... and using something else for the SQL has made it simpler for me (and every contributor)

[szsascha]

Any chances to use the old fashioned DSPFD way?

[worksofliam]

We already do that when SQL is disabled.

[worksofliam]

Also: please see issue #91. That is why we use SQL here when it is enabled.

[eduardomarco]

I will be a good beta tester, lol!

[worksofliam]

In the latest release, I have added a new output channel so we can see the results of commands.

1. Enable SQL
2. Restart Code for IBM i
3. Open the new output channel (shown in the image)
4. Try and open the member list
5. Paste the command and the results here

This will be a start :)

[eduardomarco]

/home/EAMARCO: DB2UTIL_JSON_CONTAINER=array /QOpenSys/pkgs/bin/db2util -o json " Select (Avgrowsize - 12) as MBMXRL, Iasp_Number as MBASP, System_Table_Member as MBNAME, Source_Type as MBSEU2, Partition_Text as MBMTXT From Qsys2.Syspartitionstat, Qsys2.Sysschemas Where Table_Schema = 'EAMARCO' And Table_Name = 'QRPGLESRC' And Schema_Name = System_Table_Schema " { "code": null, "signal": null, "stdout": "[\n\n]", "stderr": "" }

[SJLennon]

FWIW, and just to duplicate the results of @eduardomarco :

From Code for IBM i output:

Reformatted it so I could paste it into a QP2TERM screen, ran it, and go this:

> /QOpenSys/pkgs/bin/db2util -version                                                                                            
  db2util 1.0.11-dev                                                                                                             
  (/home/LENNONS)->                                                                                                              
> /QOpenSys/pkgs/bin/db2util -o json "select (Avgrowsize - 12) as MBMXRL, Iasp_Number as MBASP,  System_Table_Member as MBNAME,  
  Source_Type as MBSEU2,  Partition_Text as MBMTXT From Qsys2.Syspartitionstat, Qsys2.Sysschemas  Where Table_Schema = 'LENNONS1'
  And  Table_Name = 'DEMO' And  Schema_Name = System_Table_Schema"                                                               
  {"records":[                                                                                                                   

  ]}                                                                                                                             

  (/home/LENNONS)->

And this is from PuTTY:

Using username "lennons".
lennons@pub400.com's password:
Welcome to PUB400!
Starting .profile
Current shell is /QOpenSys/usr/bin/bsh (-bsh)
 PATH: /QOpenSys/pkgs/bin:/QOpenSys/usr/bin:/usr/ccs/bin:/QOpenSys/usr/bin/X11:/usr/sbin:.:/usr/bin:/QOpenSys/pkgs/bin
PS1
(/home/LENNONS)->
(/home/LENNONS)-> /QOpenSys/pkgs/bin/db2util -o json "select (Avgrowsize - 12) as MBMXRL, Iasp_Number as MBASP,  System_Table_Member as MBNAME,
>  Source_Type as MBSEU2,  Partition_Text as MBMTXT From Qsys2.Syspartitionstat, Qsys2.Sysschemas  Where Table_Schema = 'LENNONS1'
>  And  Table_Name = 'DEMO' And  Schema_Name = System_Table_Schema"             
{"records":[

]}

(/home/LENNONS)->

[worksofliam]

More updates on this issue when testing on pub400:

Interesting point:

The statement used in Code for IBM i does not work:

DB2UTIL_JSON_CONTAINER=array /QOpenSys/pkgs/bin/db2util -o json "Select (Avgrowsize - 12) as MBMXRL, Iasp_Number as MBASP, System_Table_Member as MBNAME, Source_Type as MBSEU2, Partition_Text as MBMTXT From Qsys2.Syspartitionstat, Qsys2.Sysschemas Where Table_Schema = 'QSYSINC' And Table_Name = 'H' And Schema_Name = System_Table_Schema"

but this simplified statement does work:

DB2UTIL_JSON_CONTAINER=array /QOpenSys/pkgs/bin/db2util -o json "select * from Qsys2.Syspartitionstat Where Table_Schema = 'QSYSINC' And Table_Name = 'H'"

Taking out the second table from the select makes it work:

DB2UTIL_JSON_CONTAINER=array /QOpenSys/pkgs/bin/db2util -o json "Select (Avgrowsize - 12) as MBMXRL, System_Table_Member as MBNAME, Source_Type as MBSEU2, Partition_Text as MBMTXT From Qsys2.Syspartitionstat Where Table_Schema = 'QSYSINC' And Table_Name = 'H'"

But this statement does not work on pub400:

DB2UTIL_JSON_CONTAINER=array /QOpenSys/pkgs/bin/db2util -o json "Select * from Qsys2.Sysschemas where Schema_Name = 'QSYSINC'"

So the real issue is with Qsys2.Sysschemas.

[eduardomarco]

More updates on this issue when testing on pub400:

Interesting point:

The statement used in Code for IBM i does not work:

DB2UTIL_JSON_CONTAINER=array /QOpenSys/pkgs/bin/db2util -o json "Select (Avgrowsize - 12) as MBMXRL, Iasp_Number as MBASP, System_Table_Member as MBNAME, Source_Type as MBSEU2, Partition_Text as MBMTXT From Qsys2.Syspartitionstat, Qsys2.Sysschemas Where Table_Schema = 'QSYSINC' And Table_Name = 'H' And Schema_Name = System_Table_Schema"

but this simplified statement does work:

DB2UTIL_JSON_CONTAINER=array /QOpenSys/pkgs/bin/db2util -o json "select * from Qsys2.Syspartitionstat Where Table_Schema = 'QSYSINC' And Table_Name = 'H'"

Taking out the second table from the select makes it work:

DB2UTIL_JSON_CONTAINER=array /QOpenSys/pkgs/bin/db2util -o json "Select (Avgrowsize - 12) as MBMXRL, System_Table_Member as MBNAME, Source_Type as MBSEU2, Partition_Text as MBMTXT From Qsys2.Syspartitionstat Where Table_Schema = 'QSYSINC' And Table_Name = 'H'"

But this statement does not work on pub400:

DB2UTIL_JSON_CONTAINER=array /QOpenSys/pkgs/bin/db2util -o json "Select * from Qsys2.Sysschemas where Schema_Name = 'QSYSINC'"

So the real issue is with Qsys2.Sysschemas.

I use the second statement and functioning ok for me DB2UTIL_JSON_CONTAINER=array /QOpenSys/pkgs/bin/db2util -o json "select * from Qsys2.Syspartitionstat Where Table_Schema = 'EAMARCO' And Table_Name = 'QRPGLESRC'"

[worksofliam]

This statement provides the same data and works on my two systems, one of which is pub400.

I will make a PR:

DB2UTIL_JSON_CONTAINER=array /QOpenSys/pkgs/bin/db2util -o json "Select (a.Avgrowsize - 12) as MBMXRL, b.Iasp_Number as MBASP, a.System_Table_Member as MBNAME, a.Source_Type as MBSEU2, a.Partition_Text as MBMTXT From Qsys2.Syspartitionstat as a, Qsys2.systables as b Where a.Table_Schema = 'QSYSINC' And a.Table_Name = 'H' And a.SYSTEM_TABLE_SCHEMA = b.System_Table_Schema"

[martintosney]

Liam, do you think you it would be possible to use SYSTABLES.IASP_NUMBER instead please?

The issue with using SYSSCHEMAS is that it is really just a view built over the output of:

SELECT ...
FROM TABLE (QSYS2.OBJECT_STATISTICS('QSYS      ','LIB    ', NULL)) AS P

In other words, it returns the details of every library on the system, and then filters down the list afterwards. Our system has about 177K libraries on it, so this is pretty slow 🙂

This, on the other hand, can probe the underlying table directly and runs much quicker:

SELECT
  (b.avgrowsize - 12) as MBMXRL,
  a.iasp_number as MBASP,
  b.system_table_member as MBNAME,
  b.source_type as MBSEU2,
  b.partition_text as MBMTXT
FROM qsys2.systables AS a
  JOIN qsys2.syspartitionstat AS b
    ON b.table_schema = a.table_schema AND
       b.table_name = a.table_name
WHERE
  a.table_schema = 'QSYSINC' And
  a.table_name = 'H'

(The keen-eyed might notice a discrepancy in the total number of results selected. This just seems to be Visual Explain being a bit quirky on the first query - doing a COUNT(*) on both versions shows they return the same number of results.)

[worksofliam]

Hey @onewheelonly!

Looks like we both commented near the same time. The plan is to use SYSTABLES from now on - thank you for sharting that SQL statement!

[martintosney]

I had the issue page open for a bit and missed your reply. Great minds, and all that, haha

[worksofliam]

0.5.5 is now deploying. Please try this again and raise a note here if it does not work.

Thanks!

[SJLennon]

Is"enableSQL" obsolete? My pub400 session has "enableSQL": falseand it now opens the Database Browser. Just curious. It is still mentioned in the help. @worksofliam

[worksofliam]

Weird! Let me check that out.

[eduardomarco]

All OK, now. Thanks..