DB2 table schema issue

[jpmyob]

Nature of the issue

Following the discovery process, and creating a model from that discovery, I have a model in the common/models dir. it was read using discovery-and-build.js which specified a "schema"

My db2 database makes wide use of table schemas for organization, so the table in question is @ LIVE.TBLADDRESS - when I add the model to model-config.json using

,"Tbladdress": { "dataSource": "x3", "public": true }

I get an error (where DB2X is a different schema in the database)

"statusCode": 500, "name": "Error", "message": "[IBM][CLI Driver][DB2/LINUXX8664] SQL0204N \"DB2X.TBLADDRESS\" is an undefined name. SQLSTATE=42704\r\n",

I tried to modify the config like this

,"Tbladdress": { "dataSource": "x3", "schema":"LIVE", "public": true }

But I get the exact same error.

Expected behavior

Once I specified the schema - I'd expect the API to resolve correctly

Actual behavior

The default schema for DB2 is used at all times.

Suggested resolution

Maybe I set it in the wrong place, I will continue to look for the information, It is possible I am missing something.

This is what I "see" using DB Viewer...so you have an idea what I'm referring to. DEV - host:50000/DEV -schemas |-AAA |-BBB |-DB2X (this is the schema that the error is referring to...but NOT the one specified in the model) |-DDD |-LIVE (this is the correct schema) |--Tables |--|-TBLA |--|-TBLADDRESS |-ZZZ

If it helps - here is some more detail, This is a default API install of loopback, No manually defined models - the ONLY thing i did was run this script to discover one table - This generated the /common/models/Tbladdress.json file below.

var path = require('path');
var fs = require('fs');
var app = require(path.resolve(__dirname, '../server'));
var outputPath = path.resolve(__dirname, '../../common/models');

var dataSource = app.dataSources.accountDs;

function schemaCB(err, schema) {
    if(schema) {
        console.log("Auto discovery success: " + schema.name);
        var outputName = outputPath + '/' +schema.name + '.json';
        fs.writeFile(outputName, JSON.stringify(schema, null, 2), function(err) {
            if(err) {
                console.log(err);
            } else {
                console.log("JSON saved to " + outputName);
            }
        });
    }
    if(err) {
        console.error(err);
        return;
    }
    return;
};

dataSource.discoverSchema('TBLADDRESS',{schema:'LIVE'},schemaCB);

These are my config files, and model

/common/models/Tbladdress.json

{
    "name": "Tbladdress",
    "options": {
        "idInjection": false,
        "db2": {
            "schema": "LIVE",
            "table": "TBLADDRESS"
        }
    },
    "properties": {
        ...
    }
}

/datasources.json

{
    "db": {
        "name": "db",
        "connector": "memory"
    },
    "x3": {
        "name": "x3",
        "connector": "db2",
        "username": "...",
        "password": "...",
        "database": "...",
        "hostname": "...",
        "port":     50000
    }
}

/model-config.json

{
    "_meta": {
    ...
    },
    "User": {
        "dataSource": "db"
    },
    "AccessToken": {
        "dataSource": "db",
        "public": false
    },
    "ACL": {
        "dataSource": "db",
        "public": false
    },
    "RoleMapping": {
        "dataSource": "db",
        "public": false,
        "options": {
            "strictObjectIDCoercion": true
        }
    },
    "Role": {
        "dataSource": "db",
        "public": false
    }

    ,"Tbladdress": {
        "dataSource": "x3",
        "public": true
    }

}

http://localhost:3000/explorer/#!/Tbladdress/Tbladdress_findById

{
    "error": {
        "statusCode": 500,
        "name": "Error",
        "message": "[IBM][CLI Driver][DB2/LINUXX8664] SQL0204N  \"DB2X.TBLADDRESS\" is an undefined name.  SQLSTATE=42704\r\n",
        "errors": [],
        "error": "[node-ibm_db] SQL_ERROR",
        "state": "42S02",
        "stack": "Error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N  \"DB2X.TBLADDRESS\" is an undefined name.  SQLSTATE=42704\r\n"
    }
}

...Headers...

{
    "date": "Sun, 18 Feb 2018 05:20:36 GMT",
    "x-content-type-options": "nosniff",
    "x-download-options": "noopen",
    "x-frame-options": "DENY",
    "content-type": "application/json; charset=utf-8",
    "transfer-encoding": "chunked",
    "connection": "keep-alive",
    "access-control-allow-credentials": "true",
    "vary": "Origin, Accept-Encoding",
    "x-xss-protection": "1; mode=block"
}

[jpmyob]

OK - this works -

{ "db": { "name": "db", "connector": "memory" }, "x3": { "name": "x3", "connector": "db2", "username": "...", "password": "...", "database": "...", "hostname": "...", "port": 50000 "schema": "LIVE" } }

Granted - I now have to specify a separate datasource entry for each schema (40 and counting...) but at least it's a solution, I still don't think it's the way it "should" work, because you specify the schema in the model...