How do set the library/schema list

[abmusse]

Original report by Kristopher Baehr (Bitbucket: krisbaehr, GitHub: krisbaehr).

---

How can we set the library/schema list for a particular connection or statement? I'm having difficulty finding examples or documentation. To clarify, this is not the default library when one is not provided, but the full list ie. "LIB1, LIB2, LIB3." Thanks!

[abmusse]

Original comment by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).

---

@krisbaehr , check out this slide from my "Node.js All The Things" presentation.

This tells the connection to use the *LIBL as specified by the user's *JOBD. Not necessarily exactly what you're asking for, but hopefully it gives some addtl insight.

[abmusse]

Original comment by Kristopher Baehr (Bitbucket: krisbaehr, GitHub: krisbaehr).

---

Thanks, @aaronbartell . I'm already specifying the SQL_ATTR_DBC_SYS_NAMING connection attribute, but it helps to know exactly what that's doing. It seems like this would be a nice feature to have at some point. I think the workaround would be to control the library list by specifying different users on the connection.

[abmusse]

Original comment by Jesse G (Bitbucket: ThePrez, GitHub: ThePrez).

---

@abmusse, is there yet a way to do this?

I suspect at least a call to QCMDEXC with a "CHGLIBL" command could do it. Will you look into that?

[abmusse]

@ThePrez Will do!

[abmusse]

@krisbaehr

Calling QSYS2.QCMDEXC(?) stored procedure to execute the CHGLIBL CL command will change your library list.

For example:

const { dbstmt, dbconn, IN, CHAR } = require('idb-connector');

let conn = new dbconn();

conn.conn('*LOCAL');

let changeLibStmt = new dbstmt(conn),
    qcmdexc = 'CALL QSYS2.QCMDEXC(?)',
    // you can set multiple libs
    changeLibParam = 'CHGLIBL (TESTLIB TESTLIB2)';

changeLibStmt.prepare(qcmdexc, (error) => {
    if (error) {
        throw error;
    }
    changeLibStmt.bindParam([[changeLibParam, IN, CHAR]], error => {
        if (error) {
            throw error;
        }
        changeLibStmt.execute((out, error) => {
            if (error) {
                throw error;
            }
            changeLibStmt.close();
        });
    });
});

You can view current library list from qsys2.library_list_info view.

For Example:

const {dbstmt, dbconn} = require('idb-connector');

let conn = new dbconn();

conn.conn('*LOCAL');

let viewLibStmt = new dbstmt(conn),
   viewLibList = 'SELECT * from qsys2.library_list_info';

viewLibStmt.exec(viewLibList, (result, error) => {
    if (error){
        throw error;
    }
    console.log(result)
    viewLibStmt.close();
});

Before CHGLIBL command:

[ { ORDINAL_POSITION: '1',
    SCHEMA_NAME: 'QSYS',
    SYSTEM_SCHEMA_NAME: 'QSYS',
    TYPE: 'SYSTEM',
    IASP_NUMBER: '0',
    TEXT_DESCRIPTION: 'System Library' },
  { ORDINAL_POSITION: '2',
    SCHEMA_NAME: 'QSYS2',
    SYSTEM_SCHEMA_NAME: 'QSYS2',
    TYPE: 'SYSTEM',
    IASP_NUMBER: '0',
    TEXT_DESCRIPTION: 'System Library for CPI\'s' },
  { ORDINAL_POSITION: '3',
    SCHEMA_NAME: 'QHLPSYS',
    SYSTEM_SCHEMA_NAME: 'QHLPSYS',
    TYPE: 'SYSTEM',
    IASP_NUMBER: '0',
    TEXT_DESCRIPTION: null },
  { ORDINAL_POSITION: '4',
    SCHEMA_NAME: 'QUSRSYS',
    SYSTEM_SCHEMA_NAME: 'QUSRSYS',
    TYPE: 'SYSTEM',
    IASP_NUMBER: '0',
    TEXT_DESCRIPTION: 'System Library for Users' },
  { ORDINAL_POSITION: '5',
    SCHEMA_NAME: 'QGPL',
    SYSTEM_SCHEMA_NAME: 'QGPL',
    TYPE: 'USER',
    IASP_NUMBER: '0',
    TEXT_DESCRIPTION: 'General Purpose Library' },
  { ORDINAL_POSITION: '6',
    SCHEMA_NAME: 'QTEMP',
    SYSTEM_SCHEMA_NAME: 'QTEMP',
    TYPE: 'USER',
    IASP_NUMBER: '0',
    TEXT_DESCRIPTION: null },
  { ORDINAL_POSITION: '7',
    SCHEMA_NAME: 'QDEVELOP',
    SYSTEM_SCHEMA_NAME: 'QDEVELOP',
    TYPE: 'USER',
    IASP_NUMBER: '0',
    TEXT_DESCRIPTION: null },
  { ORDINAL_POSITION: '8',
    SCHEMA_NAME: 'QBLDSYS',
    SYSTEM_SCHEMA_NAME: 'QBLDSYS',
    TYPE: 'USER',
    IASP_NUMBER: '0',
    TEXT_DESCRIPTION: null },
  { ORDINAL_POSITION: '9',
    SCHEMA_NAME: 'QBLDSYSR',
    SYSTEM_SCHEMA_NAME: 'QBLDSYSR',
    TYPE: 'USER',
    IASP_NUMBER: '0',
    TEXT_DESCRIPTION: null } ]

After CHGLIBL

[ { ORDINAL_POSITION: '1',
    SCHEMA_NAME: 'QSYS',
    SYSTEM_SCHEMA_NAME: 'QSYS',
    TYPE: 'SYSTEM',
    IASP_NUMBER: '0',
    TEXT_DESCRIPTION: 'System Library' },
  { ORDINAL_POSITION: '2',
    SCHEMA_NAME: 'QSYS2',
    SYSTEM_SCHEMA_NAME: 'QSYS2',
    TYPE: 'SYSTEM',
    IASP_NUMBER: '0',
    TEXT_DESCRIPTION: 'System Library for CPI\'s' },
  { ORDINAL_POSITION: '3',
    SCHEMA_NAME: 'QHLPSYS',
    SYSTEM_SCHEMA_NAME: 'QHLPSYS',
    TYPE: 'SYSTEM',
    IASP_NUMBER: '0',
    TEXT_DESCRIPTION: null },
  { ORDINAL_POSITION: '4',
    SCHEMA_NAME: 'QUSRSYS',
    SYSTEM_SCHEMA_NAME: 'QUSRSYS',
    TYPE: 'SYSTEM',
    IASP_NUMBER: '0',
    TEXT_DESCRIPTION: 'System Library for Users' },
  { ORDINAL_POSITION: '5',
    SCHEMA_NAME: 'TESTLIB',
    SYSTEM_SCHEMA_NAME: 'TESTLIB',
    TYPE: 'USER',
    IASP_NUMBER: '0',
    TEXT_DESCRIPTION: null },
  { ORDINAL_POSITION: '6',
    SCHEMA_NAME: 'TESTLIB2',
    SYSTEM_SCHEMA_NAME: 'TESTLIB2',
    TYPE: 'USER',
    IASP_NUMBER: '0',
    TEXT_DESCRIPTION: null } ]

[abmusse]

Original comment by Kristopher Baehr (Bitbucket: krisbaehr, GitHub: krisbaehr).

---

@abmusse Perfect, thanks!