How do set the library/schema list
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!
Comments (7)
-
-
reporter 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.
-
-
assigned issue to
@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?
-
assigned issue to
-
@ThePrez Will do!
-
@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 } ]
-
reporter @abmusse Perfect, thanks!
-
- changed status to resolved
- Log in to comment
@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.