general look feel of "async"

[kadler]

Original report by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Asynchronous Execution

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

db2sock work in progress (serious work). Closing this thread. Please read goals on overview. This is the target design until i hear different. Will re-open issues after basic examples are working.

[kadler]

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

---

Do you start getting charged by the bits-per-pound, aka, too many diff commits, and, this project gets killed by admins of Bitbucket ???

They only charge for code that has memory leaks. So as long as we stay away from that we should be fine.

JUST KIDDING :-)

There are no extra charges. Generate away.

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Cool stuff!

So, needless to say ... indications are good for async db2 driver project.

Now, i am looking at maybe creating better quantum functions, aka, current examples map everything to character on the c side. Mmmm ... no promises ... but maybe worth a bit of effort, MAYBE, 'significant function' async activities can occur .

Is it possible to start putting this code in the repo?

Thinking about this git repository, so you can see the code ... mmm ... because most of this code is generated, vast number of changes can happen in a click of a button. I wonder, what happens to a 'git repository' when massive amount 'diff' c code happens 8 times a day. Do you start getting charged by the bits-per-pound, aka, too many diff commits, and, this project gets killed by admins of Bitbucket ???

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Or, how about something in a prepare execute (belt and suspenders) ...

#!shell

bash-4.3$ test0005_async_callback_query_marker_32
main_environ (thread 1): starting
main_environ (thread 1): leaving
SQL400EnvironmentCallback (thread 258): starting
SQL400EnvironmentCallback (thread 258): complete: sqlrc=0, *ohnd=1 options=20000b88 callback=20000a54
main_connect (thread 258): starting
main_connect (thread 258): leaving
SQL400EnvironmentCallback (thread 258): leaving
SQL400ConnectCallback (thread 515): starting
SQL400ConnectCallback (thread 515): complete: sqlrc=0, henv=1, db=*LOCAL uid=DB2 pwd=xxxx *ohnd=2 options=20034cd0 callback=20000a78
main_prepare (thread 515): starting
main_prepare (thread 515): leaving
SQL400ConnectCallback (thread 515): leaving
SQLPrepareCallback (thread 772): starting
SQLPrepareCallback (thread 772): complete: sqlrc=0, hstmt=3, sql=select * from qiws.qcustcdt where lstnam like ? len=47 callback=20000a6c
main_execute (thread 772): starting
main_execute (thread 772): leaving
SQLPrepareCallback (thread 772): leaving
SQL400ExecuteCallback (thread 1029): starting
SQL400ExecuteCallback (thread 1029): complete: sqlrc=0, hstmt=3, parms=20034688 desc_parms=2001ad28 callback=20000a60
main_fetch (thread 1029): starting
main_fetch (thread 1029): nbr cols 11
main_fetch (thread 1029): CUSNUM [938472 (bb)](https://bitbucket.org/litmis/db2sock/commits/938472)
main_fetch (thread 1029): LSTNAM Henning 
main_fetch (thread 1029): INIT G K
main_fetch (thread 1029): STREET 4859 Elm Ave 
main_fetch (thread 1029): CITY Dallas
main_fetch (thread 1029): STATE TX
main_fetch (thread 1029): ZIPCOD 75217
main_fetch (thread 1029): CDTLMT 5000
main_fetch (thread 1029): CHGCOD 3
main_fetch (thread 1029): BALDUE 37.00
main_fetch (thread 1029): CDTDUE .00
main_fetch (thread 1029): leaving
SQL400ExecuteCallback (thread 1029): leaving
bash-4.3$

c code snip ...

#!c

char * qry1 = "select * from qiws.qcustcdt where lstnam like ?";
char * prm1 = "Hen%";

/* ====================
 * fetch
 * ====================
 */

void main_fetch(SQLHANDLE hstmt) {
  pthread_t ptid = pthread_self();
  pthread_t tid = 0;
  SQLRETURN sqlrc = SQL_SUCCESS;
  SQLSMALLINT pccol = 0;
  SQLINTEGER start_row = 0;
  int i = 0;
  printf("main_fetch (thread %d): starting\n",ptid);
  sqlrc = SQLNumResultCols(hstmt, (SQLSMALLINT *)&pccol );
  lang_check_sqlrc(SQL_HANDLE_STMT, hstmt, sqlrc, 1, &sqlcode);
  printf("main_fetch (thread %d): nbr cols %d\n",ptid, pccol);
  for (i=0;i<pccol;i++) {
    sqlrc = SQL400AddDesc(hstmt, i + 1, SQL400_DESC_COL, (SQLPOINTER)&desc_cols);
    lang_check_sqlrc(SQL_HANDLE_STMT, hstmt, sqlrc, 1, &sqlcode);
    data_cols[i] = (SQLPOINTER) malloc(1024);
    indPtr = 1024;
    sqlrc = SQL400AddCParam(i + 1, 0, SQL_C_CHAR, (SQLPOINTER)data_cols[i], &indPtr, (SQLPOINTER) &call_cols);
  }
  sqlrc = SQL400Fetch(hstmt, start_row, (SQLPOINTER)&call_cols, (SQLPOINTER)&desc_cols);
  lang_check_sqlrc(SQL_HANDLE_STMT, hstmt, sqlrc, 1, &sqlcode);
  for (i=0;i<pccol;i++) {
    printf("main_fetch (thread %d): %s %s\n", ptid, (char *)desc_cols[i].szColName, (char *)data_cols[i]);
  }
  printf("main_fetch (thread %d): leaving\n",ptid);
}

/* ====================
 * execute
 * select * from qiws/qcustcdt where lstnam like 'Hen%'
 * ====================
 */

/* callback sends SQL400ExecuteStruct (PaseCliAsync.h) */
void SQL400ExecuteCallback(SQL400ExecuteStruct* myptr) {
  pthread_t ptid = pthread_self();
  pthread_t tid = 0;
  printf("SQL400ExecuteCallback (thread %d): starting\n",ptid);
  printf("SQL400ExecuteCallback (thread %d): complete: sqlrc=%d, hstmt=%d, parms=%p desc_parms=%p callback=%p\n",
    ptid, myptr->sqlrc, myptr->hstmt, myptr->parms, myptr->desc_parms, myptr->callback);
  lang_check_sqlrc(SQL_HANDLE_STMT, myptr->hstmt, myptr->sqlrc, 1, &sqlcode);
  /* execute done, next fetch ...*/
  main_fetch(myptr->hstmt);
  free(myptr);
  printf("SQL400ExecuteCallback (thread %d): leaving\n",ptid);
}

void main_execute(SQLHANDLE hstmt) {
  pthread_t ptid = pthread_self();
  pthread_t tid = 0;
  SQLRETURN sqlrc = SQL_SUCCESS;
  printf("main_execute (thread %d): starting\n",ptid);
  sqlrc = SQL400AddDesc(hstmt, 1, SQL400_DESC_PARM, (SQLPOINTER)&desc_parms);
  lang_check_sqlrc(SQL_HANDLE_STMT, hstmt, sqlrc, 1, &sqlcode);
  indPtr = strlen(prm1);
  sqlrc = SQL400AddCParam(1, SQL_PARAM_INPUT, SQL_C_CHAR, (SQLPOINTER)prm1, &indPtr, (SQLPOINTER) &call_parms ); 
  tid = SQL400ExecuteAsync(hstmt, (SQLPOINTER)&call_parms, (SQLPOINTER)&desc_parms, (void *)SQL400ExecuteCallback);
  printf("main_execute (thread %d): leaving\n",ptid);
}

/* ====================
 * prepare
 * select * from qiws/qcustcdt where lstnam like 'Hen%'
 * ====================
 */

/* callback sends SQLPrepareStruct (PaseCliAsync.h) */
void SQLPrepareCallback(SQLPrepareStruct* myptr) {
  pthread_t ptid = pthread_self();
  pthread_t tid = 0;
  printf("SQLPrepareCallback (thread %d): starting\n",ptid);
  printf("SQLPrepareCallback (thread %d): complete: sqlrc=%d, hstmt=%d, sql=%s len=%d callback=%p\n",
    ptid, myptr->sqlrc, myptr->hstmt, myptr->szSqlStr, myptr->cbSqlStr, myptr->callback);
  lang_check_sqlrc(SQL_HANDLE_STMT, myptr->hstmt, myptr->sqlrc, 1, &sqlcode);
  /* prepare done, next execute ...*/
  main_execute(myptr->hstmt);
  free(myptr);
  printf("SQLPrepareCallback (thread %d): leaving\n",ptid);
}

void main_prepare(SQLHANDLE hdbc) {
  pthread_t ptid = pthread_self();
  pthread_t tid = 0;
  SQLRETURN sqlrc = SQL_SUCCESS;
  printf("main_prepare (thread %d): starting\n",ptid);
  sqlrc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
  lang_check_sqlrc(SQL_HANDLE_STMT, hstmt, sqlrc, 1, &sqlcode);
  tid = SQLPrepareAsync (hstmt, (SQLCHAR *)qry1, strlen(qry1), (void *)SQLPrepareCallback );
  printf("main_prepare (thread %d): leaving\n",ptid);
}

/* ====================
 * connection
 * ====================
 */

/* callback sends SQL400ConnectStruct (PaseCliAsync.h) */
void SQL400ConnectCallback(SQL400ConnectStruct* myptr) {
  pthread_t ptid = pthread_self();
  pthread_t tid = 0;
  printf("SQL400ConnectCallback (thread %d): starting\n",ptid);
  printf("SQL400ConnectCallback (thread %d): complete: sqlrc=%d, henv=%d, db=%s uid=%s pwd=xxxx *ohnd=%d options=%p callback=%p\n",
    ptid, myptr->sqlrc, myptr->henv, myptr->db, myptr->uid, *(myptr->ohnd), myptr->options, myptr->callback);
  lang_check_sqlrc(SQL_HANDLE_DBC, *(myptr->ohnd), myptr->sqlrc, 1, &sqlcode);
  /* connect done, next prepare ...*/
  main_prepare(*(myptr->ohnd));
  free(myptr);
  printf("SQL400ConnectCallback (thread %d): leaving\n",ptid);
}
void main_connect(SQLHANDLE henv) {
  pthread_t ptid = pthread_self();
  pthread_t tid = 0;
  SQL400ConnectStruct *myptr = (SQL400ConnectStruct *) NULL;
  printf("main_connect (thread %d): starting\n",ptid);
  /* async connection */
  tid = SQL400ConnectAsync(henv, (char *)&db, (char *)&uid, (char *)&pwd, &hdbc, (SQLPOINTER)&pophdbc, (void *)SQL400ConnectCallback);
  printf("main_connect (thread %d): leaving\n",ptid);
}

[kadler]

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

---

Cool stuff!

Is it possible to start putting this code in the repo?

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Something a little more interesting ... cool (if i may say) ...

#!shell

bash-4.3$ ./test0004_async_callback_query_32 
main_environ (thread 1): starting
main_environ (thread 1): leaving
SQL400EnvironmentCallback (thread 258): starting
SQL400EnvironmentCallback (thread 258): complete: sqlrc=0, *ohnd=1 options=20000768 callback=20000654
main_connect (thread 258): starting
main_connect (thread 258): leaving
SQL400EnvironmentCallback (thread 258): leaving
SQL400ConnectCallback (thread 515): starting
SQL400ConnectCallback (thread 515): complete: sqlrc=0, henv=1, db=*LOCAL uid=DB2 pwd=xxxx *ohnd=2 options=2001a910 callback=2000066c
main_query (thread 515): starting
main_query (thread 515): leaving
SQL400ConnectCallback (thread 515): leaving
SQLExecDirectCallback (thread 772): starting
SQLExecDirectCallback (thread 772): complete: sqlrc=0, hstmt=3, sql=select * from qiws.qcustcdt where lstnam like 'Hen%' len=52 callback=20000660
main_fetch (thread 772): starting
main_fetch (thread 772): CUSNUM [938472 (bb)](https://bitbucket.org/litmis/db2sock/commits/938472)
main_fetch (thread 772): LSTNAM Henning 
main_fetch (thread 772): INIT G K
main_fetch (thread 772): STREET 4859 Elm Ave 
main_fetch (thread 772): CITY Dallas
main_fetch (thread 772): STATE TX
main_fetch (thread 772): ZIPCOD 75217
main_fetch (thread 772): CDTLMT 5000
main_fetch (thread 772): CHGCOD 3
main_fetch (thread 772): BALDUE 37.00
main_fetch (thread 772): CDTDUE .00
main_fetch (thread 772): leaving
SQLExecDirectCallback (thread 772): leaving
bash-4.3$ 

The c code

#!c

char * qry0 = "select * from qiws.qcustcdt where lstnam like 'Hen%'";

/* ====================
 * fetch
 * ====================
 */

void main_fetch(SQLHANDLE hstmt) {
  pthread_t ptid = pthread_self();
  pthread_t tid = 0;
  SQLRETURN sqlrc = SQL_SUCCESS;
  SQLSMALLINT pccol = 0;
  SQLINTEGER start_row = 0;
  int i = 0;
  printf("main_fetch (thread %d): starting\n",ptid);
  sqlrc = SQLNumResultCols(hstmt, (SQLSMALLINT *)&pccol );
  lang_check_sqlrc(SQL_HANDLE_STMT, hstmt, sqlrc, 1, &sqlcode);
  for (i=0;i<pccol;i++) {
    sqlrc = SQL400AddDesc(hstmt, i + 1, SQL400_DESC_COL, (SQLPOINTER)&desc_cols);
    lang_check_sqlrc(SQL_HANDLE_STMT, hstmt, sqlrc, 1, &sqlcode);
    data_cols[i] = (SQLPOINTER) malloc(1024);
    indPtr = 1024;
    sqlrc = SQL400AddCParam(i + 1, 0, SQL_C_CHAR, (SQLPOINTER)data_cols[i], &indPtr, (SQLPOINTER) &call_cols);
  }
  sqlrc = SQL400Fetch(hstmt, start_row, (SQLPOINTER)&call_cols, (SQLPOINTER)&desc_cols);
  lang_check_sqlrc(SQL_HANDLE_STMT, hstmt, sqlrc, 1, &sqlcode);
  for (i=0;i<pccol;i++) {
    printf("main_fetch (thread %d): %s %s\n", ptid, (char *)desc_cols[i].szColName, (char *)data_cols[i]);
  }
  printf("main_fetch (thread %d): leaving\n",ptid);
}

/* ====================
 * query
 * select * from qiws/qcustcdt where lstnam like 'Hen%'
 * ====================
 */

/* callback sends SQLExecDirectStruct (PaseCliAsync.h) */
void SQLExecDirectCallback(SQLExecDirectStruct* myptr) {
  pthread_t ptid = pthread_self();
  pthread_t tid = 0;
  printf("SQLExecDirectCallback (thread %d): starting\n",ptid);
  printf("SQLExecDirectCallback (thread %d): complete: sqlrc=%d, hstmt=%d, sql=%s len=%d callback=%p\n",
    ptid, myptr->sqlrc, myptr->hstmt, myptr->szSqlStr, myptr->cbSqlStr, myptr->callback);
  lang_check_sqlrc(SQL_HANDLE_STMT, myptr->hstmt, myptr->sqlrc, 1, &sqlcode);
  /* query done, next fetch ...*/
  main_fetch(myptr->hstmt);
  free(myptr);
  printf("SQLExecDirectCallback (thread %d): leaving\n",ptid);
}

void main_query(SQLHANDLE hdbc) {
  pthread_t ptid = pthread_self();
  pthread_t tid = 0;
  SQLRETURN sqlrc = SQL_SUCCESS;
  printf("main_query (thread %d): starting\n",ptid);
  sqlrc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
  lang_check_sqlrc(SQL_HANDLE_STMT, hstmt, sqlrc, 1, &sqlcode);
  tid = SQLExecDirectAsync (hstmt, (SQLCHAR *)qry0, strlen(qry0), (void *)SQLExecDirectCallback );
  printf("main_query (thread %d): leaving\n",ptid);
}

/* ====================
 * connection
 * ====================
 */

/* callback sends SQL400ConnectStruct (PaseCliAsync.h) */
void SQL400ConnectCallback(SQL400ConnectStruct* myptr) {
  pthread_t ptid = pthread_self();
  pthread_t tid = 0;
  printf("SQL400ConnectCallback (thread %d): starting\n",ptid);
  printf("SQL400ConnectCallback (thread %d): complete: sqlrc=%d, henv=%d, db=%s uid=%s pwd=xxxx *ohnd=%d options=%p callback=%p\n",
    ptid, myptr->sqlrc, myptr->henv, myptr->db, myptr->uid, *(myptr->ohnd), myptr->options, myptr->callback);
  lang_check_sqlrc(SQL_HANDLE_DBC, *(myptr->ohnd), myptr->sqlrc, 1, &sqlcode);
  /* connect done, next query ...*/
  main_query(*(myptr->ohnd));
  free(myptr);
  printf("SQL400ConnectCallback (thread %d): leaving\n",ptid);
}
void main_connect(SQLHANDLE henv) {
  pthread_t ptid = pthread_self();
  pthread_t tid = 0;
  SQL400ConnectStruct *myptr = (SQL400ConnectStruct *) NULL;
  printf("main_connect (thread %d): starting\n",ptid);
  /* async connection */
  tid = SQL400ConnectAsync(henv, (char *)&db, (char *)&uid, (char *)&pwd, &hdbc, (SQLPOINTER)&pophdbc, (void *)SQL400ConnectCallback);
  printf("main_connect (thread %d): leaving\n",ptid);
}

/* ====================
 * environment
 * ====================
 */

/* callback sends SQL400EnvironmentStruct (PaseCliAsync.h) */
void SQL400EnvironmentCallback(SQL400EnvironmentStruct* myptr) {
  pthread_t ptid = pthread_self();
  pthread_t tid = 0;
  printf("SQL400EnvironmentCallback (thread %d): starting\n",ptid);
  printf("SQL400EnvironmentCallback (thread %d): complete: sqlrc=%d, *ohnd=%d options=%p callback=%p\n",
    ptid, myptr->sqlrc, *(myptr->ohnd), myptr->options, myptr->callback);
  lang_check_sqlrc(SQL_HANDLE_DBC, *(myptr->ohnd), myptr->sqlrc, 1, &sqlcode);
  /* environ done, next connect ... */
  main_connect(*(myptr->ohnd));
  free(myptr);
  printf("SQL400EnvironmentCallback (thread %d): leaving\n",ptid);
}
void main_environ() {
  pthread_t ptid = pthread_self();
  pthread_t tid = 0;
  SQLRETURN sqlrc = SQL_SUCCESS;
  printf("main_environ (thread %d): starting\n",ptid);
  /* async environment db2 */
  sqlrc = SQL400AddAttr(SQL_HANDLE_ENV, SQL400_ATTR_CCSID, &ccsid, 0, SQL400_ONERR_CONT, SQL400_FLAG_IMMED, (SQLPOINTER)&pophenv);
  sqlrc = SQL400AddAttr(SQL_HANDLE_ENV, SQL_ATTR_SERVER_MODE, &yes, 0, SQL400_ONERR_CONT, SQL400_FLAG_IMMED, (SQLPOINTER)&pophenv);
  tid = SQL400EnvironmentAsync( &henv, (SQLPOINTER)&pophenv, (void *)SQL400EnvironmentCallback);
  printf("main_environ (thread %d): leaving\n",ptid);
}

/* ====================
 * main
 * ====================
 */
int main(int argc, char * argv[]) {
  pthread_t ptid = pthread_self();
  pthread_t tid = 0;
  SQLRETURN sqlrc = SQL_SUCCESS;
  main_environ();
  sleep(20);

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Ok, still changing things a bit ...

1) Bit better testing ...

#!shell

bash-4.3$ test0002_async_reap_connect_32
SQL400ConnectAsync (thread 258): connect running
SQL400ConnectJoin (thread 258): complete: sqlrc=0, henv=1, db=*LOCAL uid=DB2 pwd=xxxx *ohnd=2 options=20000b98 callback=0
bash-4.3$ test0002_async_reap_connect_64
SQL400ConnectAsync (thread 258): connect running
SQL400ConnectJoin (thread 258): complete: sqlrc=0, henv=1, db=*LOCAL uid=DB2 pwd=xxxx *ohnd=2 options=180000da8 callback=0
bash-4.3$ test0003_async_callback_connect_32
SQL400ConnectAsync (thread 258): connect running
SQL400ConnectAsync (thread 1): hi there from main thread
SQL400ConnectCallback (thread 258): hi there from callback thread
SQL400ConnectCallback (thread 258): complete: sqlrc=0, henv=1, db=*LOCAL uid=DB2 pwd=xxxx *ohnd=2 options=20000cb0 callback=20000b84
bash-4.3$ test0003_async_callback_connect_64
SQL400ConnectAsync (thread 258): connect running
SQL400ConnectAsync (thread 1): hi there from main thread
SQL400ConnectCallback (thread 258): hi there from callback thread
SQL400ConnectCallback (thread 258): complete: sqlrc=0, henv=1, db=*LOCAL uid=DB2 pwd=xxxx *ohnd=2 options=180000ed0 callback=180000cd8

#!c

void SQL400ConnectCallback(SQL400ConnectStruct* myptr) {
  pthread_t ptid = pthread_self();
  printf("SQL400ConnectCallback (thread %d): hi there from callback thread\n",ptid);
  printf("SQL400ConnectCallback (thread %d): complete: sqlrc=%d, henv=%d, db=%s uid=%s pwd=xxxx *ohnd=%d options=%p callback=%p\n",
    ptid, myptr->sqlrc, myptr->henv, myptr->db, myptr->uid, *(myptr->ohnd), myptr->options, myptr->callback);
  lang_check_sqlrc(SQL_HANDLE_DBC, *(myptr->ohnd), myptr->sqlrc, 1, &sqlcode);
  free(myptr);
}

int main(int argc, char * argv[]) {
  SQLRETURN sqlrc = SQL_SUCCESS;
  pthread_t tid = 0;
  SQL400ConnectStruct *myptr = (SQL400ConnectStruct *) NULL;
  pthread_t ptid = pthread_self();
  /* environment db2 */
  sqlrc = SQL400AddAttr(SQL_HANDLE_ENV, SQL400_ATTR_CCSID, &ccsid, 0, SQL400_ONERR_CONT, SQL400_FLAG_IMMED, (SQLPOINTER)&pophenv);
  sqlrc = SQL400AddAttr(SQL_HANDLE_ENV, SQL_ATTR_SERVER_MODE, &yes, 0, SQL400_ONERR_CONT, SQL400_FLAG_IMMED, (SQLPOINTER)&pophenv);
  sqlrc = SQL400Environment( &henv, (SQLPOINTER)&pophenv );
  lang_check_sqlrc(SQL_HANDLE_ENV, henv, sqlrc, 1, &sqlcode);
  /* async connection */
  tid = SQL400ConnectAsync(henv, (char *)&db, (char *)&uid, (char *)&pwd, &hdbc, (SQLPOINTER)&pophdbc, (void *)SQL400ConnectCallback);
  printf("SQL400ConnectAsync (thread %d): connect running\n", tid);
  printf("SQL400ConnectAsync (thread %d): hi there from main thread\n",ptid);
  sleep(5);
  sqlrc = SQLDisconnect(hdbc);
  sqlrc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
  return sqlrc;
}

2) Added join/reap with no wait ...

#!c

int main(int argc, char * argv[]) {
  SQLRETURN sqlrc = SQL_SUCCESS;
  pthread_t tid = 0;
  SQL400ConnectStruct *myptr = (SQL400ConnectStruct *) NULL;
  /* environment db2 */
  sqlrc = SQL400AddAttr(SQL_HANDLE_ENV, SQL400_ATTR_CCSID, &ccsid, 0, SQL400_ONERR_CONT, SQL400_FLAG_IMMED, (SQLPOINTER)&pophenv);
  sqlrc = SQL400AddAttr(SQL_HANDLE_ENV, SQL_ATTR_SERVER_MODE, &yes, 0, SQL400_ONERR_CONT, SQL400_FLAG_IMMED, (SQLPOINTER)&pophenv);
  sqlrc = SQL400Environment( &henv, (SQLPOINTER)&pophenv );
  lang_check_sqlrc(SQL_HANDLE_ENV, henv, sqlrc, 1, &sqlcode);
  /* async connection */
  tid = SQL400ConnectAsync(henv, (char *)&db, (char *)&uid, (char *)&pwd, &hdbc, (SQLPOINTER)&pophdbc, (void *)NULL);
  printf("SQL400ConnectAsync (thread %d): connect running\n", tid);
  myptr = SQL400ConnectJoin (tid, SQL400_FLAG_JOIN_NO_WAIT);
  if (!myptr) {
    printf("SQL400ConnectAsync (thread %d): connect still running thread %d\n", tid);
    myptr = SQL400ConnectJoin (tid, SQL400_FLAG_JOIN_WAIT);
  }
  /* join return SQL400ConnectStruct (PaseCliAsync.h) */
  printf("SQL400ConnectJoin (thread %d): complete: sqlrc=%d, henv=%d, db=%s uid=%s pwd=xxxx *ohnd=%d options=%p callback=%p\n",
    tid, myptr->sqlrc, myptr->henv, myptr->db, myptr->uid, *(myptr->ohnd), myptr->options, myptr->callback);
  lang_check_sqlrc(SQL_HANDLE_DBC, *(myptr->ohnd), myptr->sqlrc, 1, &sqlcode);
  free(myptr);
  sqlrc = SQLDisconnect(hdbc);
  sqlrc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
  return sqlrc;
}

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Well ... very few tests ... mickey mouse tests ... async callback did release the main thread while running SQL400ConnectAsync. The function work both as callback (nodejs-like) and reap (php-like).

BTW -- Yep, i made a quantum function, so i wouldn't have to write much test code, conn attr, as well as connect (couldn't resit making one).

#!shell
bash-4.3$ test0003_async_callback_connect_32
hi there from main
hi there from callback

And wrkactjob has the QSQ (it worked).

#!shell

       QSQSRVR      DB2         PJ       .0                   CNDW

Ah ... no comment on mickey mouse test code here, i used globals because i was lazy.

#!c

#include <sqlcli1.h>
#include "test.h"
#include "PaseCliAsync.h"
#include "liblang400.h"

char db[]  = { SQL_DB400 };
char uid[] = { SQL_UID400 };
char pwd[] = { SQL_PWD400 };
SQL400SetAttrStruct options[100];
SQLHANDLE henv;
SQLHANDLE hdbc;
SQLINTEGER ccsid = 819;
SQLINTEGER yes = SQL_TRUE;
SQL400AttrStruct pophenv[3];
SQL400AttrStruct pophdbc[3];

/* SQL400ConnectStruct * SQL400ConnectJoin (pthread_t tid) */
void SQL400ConnectCallback(SQL400ConnectStruct* myptr) {
  /* SQL400ConnectStruct* mytmp = NULL; */
  printf("hi there from callback\n");
  free(myptr);
  /* mytmp = SQL400ConnectJoin (tid); */
}

int main(int argc, char * argv[]) {
  SQLRETURN sqlrc = SQL_SUCCESS;
  pthread_t tid = 0;
  SQL400ConnectStruct *myptr = (SQL400ConnectStruct *) NULL;
  /* SQLRETURN SQL400AddEnvAttr( SQLHENV  hndl, SQLINTEGER  attrib, SQLPOINTER  vParam, 
        SQLINTEGER  inlen, SQLINTEGER  onerr, SQLINTEGER  flag, SQLPOINTER * options ); */
  sqlrc = SQL400AddEnvAttr( 1, SQL400_ATTR_CCSID, &ccsid, 0, SQL400_ONERR_CONT, 0, (SQLPOINTER)&pophenv);
  sqlrc = SQL400AddEnvAttr( 1, SQL_ATTR_SERVER_MODE, &yes, 0, SQL400_ONERR_CONT, 0, (SQLPOINTER)&pophenv);
  /* SQLRETURN SQL400Environment( SQLINTEGER * ohnd, SQLPOINTER  options ); */
  sqlrc = SQL400Environment( &henv, (SQLPOINTER)&pophenv );
  /* pthread_t SQL400ConnectAsync ( SQLHENV  henv, SQLCHAR * db, SQLCHAR * uid, 
       SQLCHAR * pwd, SQLINTEGER * ohnd, SQLPOINTER  options, void * callback ) */
  tid = SQL400ConnectAsync(henv, (char *)&db, (char *)&uid, (char *)&pwd, &hdbc, (SQLPOINTER)&pophdbc, (void *)SQL400ConnectCallback);
  printf("hi there from main\n");
  sleep(10);
  sqlrc = SQLDisconnect(hdbc);
  sqlrc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
  sqlrc = SQL400FreeAttr((SQLPOINTER)&pophenv);
  sqlrc = SQL400FreeAttr((SQLPOINTER)&pophdbc);
  return sqlrc;
}

Reap/Join looks like this ... again ... SQL400ConnectAsync let main thread go (php would run), and, SQL400ConnectJoin harvested the return data. Yes, join will block if operation is still in progress ... just like MySqli2 manual says for PHP. So, this is what is meant by user beware, you have async, then do something async before you go back and join the running secondary thread.

#!c

  /* pthread_t SQL400ConnectAsync ( SQLHENV  henv, SQLCHAR * db, SQLCHAR * uid, 
       SQLCHAR * pwd, SQLINTEGER * ohnd, SQLPOINTER  options, void * callback ) */
  tid = SQL400ConnectAsync(henv, (char *)&db, (char *)&uid, (char *)&pwd, &hdbc, (SQLPOINTER)&pophdbc, (void *)NULL);
  /* SQL400ConnectStruct * SQL400ConnectJoin (pthread_t tid) */
  myptr = SQL400ConnectJoin (tid);
  free(myptr);
  sleep(10);
  sqlrc = SQLDisconnect(hdbc);

Nearly every API in CLI has an async counterpart, but, like i said, i only have a few tests.

BTW -- geek note, 95% of the code is generated by my little script python gen.py, so, well if one CLI API works, they all should work (theory).

[kadler]

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

---

i am almost done writing the code (generating 95% with python)

Cool! Would be great if you could include that (python) code as part of this repo so other people (like me) could learn from it.

Btw, I was collaborating with another IBM i ISV yesterday that is working on compiling native C/C++ Node.js extensions for IBM i. Good to see more people going deep.

However, grasshopper (both you and me), we do not have access to the nodejs db2 source code.

Still scratching my head why they closed-sourced that bit. Counter intuitive to getting community help.

And finally, sounds good on the threads front. I await your perspiration by-product (code).

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

sell me a multi-threaded vacuum cleaner.

The only change IF using more threads please vacuum cleaner, is, less locking in secondary work threads (aka, a lot more secondary threads running). But 'more secondary threads' is just for fun, it will work with only one secondary thread at a time as well ... again, the main thread is released, so event loop is still running, aka, node js will not block unless the db2 nodejs guys mess up (below).

However, grasshopper (both you and me), we do not have access to the nodejs db2 source code. We will have to work with our brothers in China if we want 'proof absolute' all works ... really i am not worried, if we get it to work for PHP and Ruby, it will work for nodejs.

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

A single Node.js process is blocked from serving subsequent requests because calls to the Node.js DB2 for i adapter/driver are not async. In Node.js, async (cough, pseudo concurrency) is as much about releasing control of the main Javascript call stack as anything.

Yep. This secondary thread idea is exactly what we are doing. Our new libdb400.a "async" for a CLI operations means exactly this, start another thread away from current executing thread (main thread in Node.js case). Remember, the main thread will be released, therein it can go about it's merry event loop way without blocking.

BTW -- i am almost done writing the code (generating 95% with python), so, maybe just wait, take a look at code up close and personal..

[kadler]

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

---

To wit, we are limited because db2 does not support 'async' on any QSQ we are trying to use. Do you understand???

I understand DB2 doesn't support async, yes.

So, yes, i am opening your multiple thread door, just a crack. We should take a look, but, please don't force the door wide open, sell me a multi-threaded vacuum cleaner.

Now I am laughing because I am starting to understand your banter on first pass (wasn't the case when we started interacting in early PowerRuby days :-)

Digression: I didn't realize how the Javascript event loop worked (and inherently Node.js) until I watched this video. That video is 26 mins long. For shorter explanation click here to see a slide from my Intro to Node.js session.

Let's back up and walk through the original need for this project (I might have a multi-threaded vacuum to sell you, not sure yet. Read on.)... A single Node.js process is blocked from serving subsequent requests because calls to the Node.js DB2 for i adapter/driver are not async. Ok, we know that, but... In Node.js, async (cough, pseudo concurrency) is as much about releasing control of the main Javascript call stack as anything. To release the callstack to process other Javascript (the next request) we need to delegate the DB2 interaction to a separate thread. This thread doesn't need to be async, it just can't be the primary Node.js thread. This thread could queue database requests and work with a single QSQ server job.

I haven't thought about how this changes things because it is very Node.js specific, I just needed to get it out of my head so we could step back and take a look.

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

I want (hope for?) a thread-per-async-call.

Midwest geek honesty compels me ... jitters just saying ... but ... theory ... we should be able run multiple threads into a single QSQ job/connection at statement level. HEY, i don't know any real world application guts to even attempt (pleasant view on a wobbling tight rope over Niagara Falls).

So, yes, i am opening your multiple thread door, just a crack. We should take a look, but, please don't force the door wide open, sell me a multi-threaded vacuum cleaner.

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Well, yes and no, we are still bound by one-and-only-one 'in use' of any given 'connection' (thread-2-QSQ) ... again ... DB2 proper does not support 'async'.

BTW -- This annoying one-at-a-time QSQ rule is exactly why i/ranger ended up at quantum_api(s). That is to say, all these different language db2 drivers control the idea of connection (and pool). Worse, control the quantum of function/operation of each request (connect, prepare, execute, fetch, etc). HEY, If control was released to a super libdb400.a ... bingo ... we could async/thread any composite operation on any old connection scheme we wanted. BUT NO ... first whispered talk of this radical idea lead directly to a chicken switch (buck, buck, buck) ... just sayin'

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

I don't want to be limited and have my async requests queue up in a single thread. I want (hope for?) a thread-per-async-call.

Well, yes and no, we are still bound by one-and-only-one 'in use' of any given 'connection' (thread-2-QSQ) ... again ... DB2 proper does not support 'async'.

So, language like PHP, not running multiple threads, only one additional supplemental 'async' thread using the 'shared connection' at a time (all we get). However, if we open up two connections in same script, each with a supplemental 'async' thread ... well ... now we have two things working 'async' at the same time. So on ... aka ... in the hands of the script writer.

Under covers same for threaded language like Ruby, with pool of connections, where only one additional 'async' thread can run on any given 'connection' aquired from the pool. However, connection pool is hidden/obfuscated by 'connection pool management', aka, loan shark lend out a connection each thread (plus one 'async' thread helper from libdb400.a). Thereby gives the impression that the world is totally 'async' crazy.

BTW -- same one-at-time per connection goes for Nodejs, but in world of callback pool of connections can be even more obfuscated.

Therefore, yes, we could design choose each SQLxxxAsync to create it's own thread, aka, a ton of 'secondary' helper threads, but acting/running only one-at-a-time on the shared connection. To wit, we are limited because db2 does not support 'async' on any QSQ we are trying to use.

Do you understand???

BTW -- we have not tried any of this yet. Mutex my connection theory is completely sensible, but, hey, maybe db2 has some grumpy rule about 'sharing' that we do not understand (heads up).

[kadler]

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

---

i just knew someone would recommend limit the number of 'supplemental' worker threads for any 'connect' to some number (aka, queue up Async requests).

I worded my original statement poorly [my Norwegian knuckles got in the way ;-)]. I mean to say I don't want to be limited and have my async requests queue up in a single thread. I want (hope for?) a thread-per-async-call.

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

threads (plural)

Completely a design 'options' debate, you, you, you, IBM i administrator you. Yes, i am falling down laughing, cause i just knew someone would recommend limit the number of 'supplemental' worker threads for any 'connect' to some number (aka, queue up Async requests).

Node js

You have a fine palette of languages my kings ... oh yes, nodejs is king with callback, hard to debug, but essence of 'async' grace after it is working.

PHP

In fact, i love PHP simplicity, any man can code up fine working scripts even send/reap async. Also, yes, much easier to mess-up and get accidentally blocked by calling on and already running SQL resource.

Yes, PHP callbacks can help 'beauty' to some extent (nodejs sense of beauty), but, PHP is not a threaded language, so, ahh, well, gee ... ok.

Ruby

Elegant Ruby. I am current spell bound under delusion that callback from Ruby may be possible ruby callback. Honestly, i can't picture exactly how to exploit at the moment. Seems like example MySql2 in Ruby is more like PHP ... mmm ... TBD

[kadler]

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

---

At end of day a different signature, aka, different header is required to 'shim' SQLExecute(old-parms, new-parms).

Agreed. I should have de-obfuscated before using the term so loosely.

All SQLxxxAsync may run in separate thread to release 'the language',

[Aaron puts on pedant hat] Do you mean all SQLxxxAsync will run in threads (plural)? Specifically, each async has their own thread so we don't have to queue multiples within a thread. If business programmer desires one SQL statement to only start after previous one finishes then they need to code for that.

In fact, 'async reap' PHP MySql2 example (previous post), comment says, will block if query is not done (mutex).

... and ...

script writer can mess up and end up blocked by 'accident'.

This might be a difference between PHP vs. Node.js expectation and approaches where the former rarely approaches coding with callbacks (though supported), and the latter lives in call-back-land. With that said, the proposed PHP syntax, when coupled with PHP callbacks, has the potential for better syntax (less complication) than Node.js (syntax junkie observation). This is more of a side comment than anything.

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

shim

Ok, hook in, fish running with line ... obfuscated term 'shim', a bit bloody PHDs yapping. At end of day a different signature, aka, different header is required to 'shim' SQLExecute(old-parms, new-parms).

As you say paraphrased "just doesn't matter' ... or ... does it (below)???

Anyway, method to madness, my version of c-code art-in-park is to introduce CLI Async APIs to add new parameters of async and callback, aka, an additional header (alternative header). This will also allow for additional aggregate functions like ...

#!c

... SQLExecuteAndSQlPutCauseWeGotLobLocatorsAsync
... SQLExecuteAfterYouCollectBunchOfInsertsOneBigBlockAsync
... SQLFetchByBlockMoreThanOneRecordButScriptWontKnowDifferenceAsync
... so on

more design still two libdb400s (why)

Again, DB2 does NOT support 'async', we must use standard thread-2-QSQ proxy model, aka, commonly known as pool of connections. All SQLxxxAsync may run in separate thread to release 'the language', BUT, are 'sharing' a connection, therefore must be synchronized (*). The common method for synchronizing thread sharing is mutex. So, yes, when a SQLxxxAsync API is off using a "connection" (thread-2-QSQ), even old non-async SQLxxx APIs have to block until completes(aka, two libdb400.a, first mutex 'all', other "as is" wild and free).

(*) For those with understanding of connection pools like Rails, number one cardinal sin is share SQL resource across threads (chaos expected). Therefore, we need to make sure an orderly "sharing" occurs during SQLxxxAsync operations (more below).

First, good news, yes, 'mutex' technique releases 'main thread' language to 'do something else'. HOWEVER, that something else 'php' better not be an action on already 'in use' connection, which will block (not always main thread, but visualization helps). In fact, 'async reap' PHP MySql2 example (previous post), comment says, will block if query is not done (mutex). To wit, thou may go do things PHPish while query is running, but don't try to use this exact resource and expect to keep async running (*)

(*) Note: MySql2 MAY be better at low scope "sharing" at statement level (i dunno) vs. DB2 must share the one-an-only-one action in any given thread-2-QSQ "connection level". That is, 'drivers' like PHP ibm_db2 have already asserted they are in control of giving out "connections", therefore our DB2 async must play the hand dealt by these "driver author' rulers (btw, quantum_apis would have changed whole game ... zillions of QSQ jobs ... ERs full of IBM i admin heart attacks after viewing WRKACTJOB ... too much ...)

Now, if you are think ahead ... yes, means "bad human" script writer can mess up and end up blocked by 'accident' (stalled actually, should never blocked forever). However, take note "good human" script writer, SQLxxxAsync good functioning scripts is dinner of Viking kings, not gruel of hairy knuckle trolls (Norwegian).

[kadler]

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

---

Curious ... 'before' is time relative to observer (Einstein). Do you mean a few days ago in this issue 'before' or something more???

Correct, 'before' as-in when we started this thread.

change to the CLI headers

And I wouldn't ever expect the CLI headers to change and instead my expectation would have been a shim approach.

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

I feel like we are somewhat doing the same thing as before (not a bad thing, just observation) by introducing SQLExecuteAsync

Curious ... 'before' is time relative to observer (Einstein). Do you mean a few days ago in this issue 'before' or something more???

SQLExecuteAsync (new API vs. adding *NOPASS-like to existing).

FYI c-code stuff ... CLI headers we copy from ILE-2-PASE (sqlcli.h), have very precise signatures (CLI architecture). Any CLI header change would be unacceptable (tar and feather, public stockade, etc.). Besides idea of *NOPASS is unique to RPG. To wit, variable parameters is handled completely different in c-code (printf below). As you can see, path of variable arguments would result in an actual change to the CLI headers (tar and feather, so on).

#!c

extern int printf (__const char *__restrict __format, ...);

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

To summarize, the pursuit will be for "threaded cli apis", and then socket DB2 in XMLSERVICE (as it relates to possible WebSocket feature addition) is a separate en devour, correct?

Correct.

[kadler]

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

---

this more modest venture is the right thing to do for this project

I am a fan of baby steps (What About Bob). So thumbs up.

I feel like we are somewhat doing the same thing as before (not a bad thing, just observation) by introducing SQLExecuteAsync (new API vs. adding *NOPASS-like to existing). Both approaches require lang driver code changes to adopt async, so six of one half-dozen of other. I am at a disadvantage because I can't see libdb400.a code.

To summarize, the pursuit will be for "threaded cli apis", and then socket DB2 in XMLSERVICE (as it relates to possible WebSocket feature addition) is a separate en devour, correct?

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Aaron, do you agree with a less ambitious plan (previous post)???

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Ok, talk meets code ... i started looking closely at PHP ibm_db2, and, well, my 70% 'rip out' and 'make-common' is now a lot less, aka, too much work. Oh well, 99% perspiration.

Status ...

socket db2

This space already available with xmlservice db2/sql via REST. In our other xmlservice 2 project, we are looking at "speed" and adding json in additio n to the xml interface. Multiple languages already take advantage of the support. In case of node.js, REST option is in fact 'async' already. You had some more good ideas like web sockets ... so good enough.

threaded quantum_api(s)

Ultimately, the world is not ready for this radical idea. Essentially, the authors of many different 'db2 drivers' across different languages would have to give up control, aka, recognize they are really doing the same operations over and over (db_connect, db_prepare, db_execute, db+close, ...). To wit, agree it is the same thing (uf da), merge all these little c-code kingdoms into a super driver (quantum libdb400.a). No! Talk of chicken switches by even us believers is sign enough that this plan is too ambitious.

threaded cli apis (like MS odbc link top)

This may be the best we can do, aka, follow the leader. Essentially, this option simply adds a set of async CLI APIs for the different language 'db2 drivers' to use at discretion (SQLExecute and SQLExecuteAsync). Languages that allow pool of connections will be fairly happy with this sort of option, control of the amount of 'async' activity is easily understood (i think). Non-threaded languages, aka, languages that do not have a pool of connections, may work similar to MySql2 php example using send/reap (see php example). Node.js, if they choose, could modify to make async call back work reasonably well. Overall i think this more modest venture is the right thing to do for this project, and, well, it is a good thing (Martha).

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Yes, though I didn't think a chicken switch was necessary

Correct, not absolutely required. Correct, very well could be the presence of super libdb400 (based on LIBPATH).

However, more chicken than you, so i wanted admin ability to flip the chicken switch willy nilly without doing much of anything, aka, env var change, and, everything goes back to normal.

Glad you told me where those floyd references were coming from because I had no clue (generation gap).

Ouch. Well, i don't have more pairs bell bottom jeans any more (he sniffles).

[kadler]

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

---

Happy now???

Yes, though I didn't think a chicken switch was necessary (maybe my lack of PASE C knowledge). With RPG I can create a SRVPGM with the same signature of initial parms and then supplement with NOPASS parms. Then have it come first in the library list so it gets picked up instead of the original. Putting it back into "super libdb400.a" scenario, couldn't the introduction of LIBPATH=/path/to/super/libdb400.a be enough to do the switch? Or is the chick switch optional because PASE/Linux people expect environment variable overrides for stuff like this?

BTW -- yep, vacation, life beyond programmer, previous 2 weeks, learned to play cords of various Pink Floyd songs over vacation (3 1/2 albums end-2-end so far).

Ah yes, music. My outlet also. I play a Taylor 414 Acoustic Guitar and an Ibanez SG bass guitar. Glad you told me where those floyd references were coming from because I had no clue (generation gap). Guess that's why I'm the grasshopper :-)

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Is it necessary to rip out? Might not be understanding what "rip" means to you. The async could be optional parm so existing lang driver remains as-is until ready to adopt. In theory one could LIBPATH=/libdb400intercept.a and it wouldn't be disruptive to drivers that only had knowledge of libdb400.a, right?

I missed part of your question ... oh ye manger-guy of chicken switch.

Answer is yes.

I am a huge fan of 'chicken switch', and, yes, completely plan to leave the current extension drivers "untouched". Essentially, a flag declaring the nature of the "chicken flag" will decide the use of the new quantum_functions, aka, chicken flag env var, symbol in only in super libdb400.a, or something like.

#!shell

export LIBDB400=shine-on-you-crazy-diamond
script adapter (lang any -- php, python, ruby adapter, etc) 
..> script driver (c-code ext -- untouched, but new by-pass added) 
....> libdb400.a (super -- optional LIBPATH=/pathtosuperlibdb400)
......> libdb400.a (pase -- same old /usr/lib/libdb400.a)

When chicken flag says shine-on-you-crazy-diamond or YES (floyd again), all traffic quantum 'async' and 'normal' will go through the new 'super' libdb400.a (get me to the good stuff).

When chicken flag says brick-in-the-wall or NO or missing and/or can't find super libdb400.a (floyd again), all traffic will be routed directly to old libdb400.a SQL interfaces, using the same old mouldy Ruby ibm_db, PHP ibm_db2, etc.

Happy now???

BTW -- yep, vacation, life beyond programmer, previous 2 weeks, learned to play cords of various Pink Floyd songs over vacation (3 1/2 albums end-2-end so far).

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Is it necessary to rip out?

I am about 70% convinced the correct move is 'rip-out-of-language-c-code' and 'make-common-c-code-libdb400' composite/quantum operations. That is to say, every language extension repeats same code over and over, multiple CLI calls, SQLSetConnectionAttr, SQLConnect, ya da, ya da, probably better served with one simple 'async' and/or 'normal' quantum_connect (int argc, char *argv[]). The trick of course copy in/out back into "variables" (c structs), that make up the language (PHP $var, $array, etc.). Again, not 100% sure, but we do know the nature of the actual CLI calls is well formed (very good architecture, millions database guys), and, c interface to languages (Ruby, PHP, etc.) are also very well formed (brilliant language creators) ... therefore ... talking out of my hat ... without actually trying anything ... well ... yeah, i am about 70% sure that 'rip-out' and 'make-common' is the way to go ....

So, we are really beyond the chatting part now, maybe just plain time to try a prototype using the PHP driver ibm_db2 (simple), then see if the resulting new quantum_api(s) also work for Ruby ibm_db.

BTW -- I am only a programmer, aka, wild duck (+ less flattering terms), anyway, not a genius, so, i am forced to live way of "... one percent inspiration and ninety-nine percent perspiration".

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Might want to have the async "key" be returned by us

First "the key" ... user shoot self in foot problem (same key mistake)

Ok, this is fine (+/- structure, aka, maybe just the key string) ...

#!php
$ret1 = db2_exec($conn, $long_running_sql, array('async'=>true));
// do other stuff ... 
$stmt = db2_reap_exec($ret1.uid);

... although in back of my mind i am thinking "smart users' may be able to do something with control over the key (maybe both methods).

[kadler]

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

---

Doing a lot of "thinking out loud"...

I have coined word 'quantum' for unit-of-async-work (**), where, essentially we want/need an 'async' abstraction API matching scripting languages APIs (see PHP below).

Might want to have the async "key" be returned by us vs. provided by business programmer because what if they provide the same key more than once? Below I simply specify I desire async and then obtain/wait for the result based on $ret1.uid.

$ret1 = db2_exec($conn, $long_running_sql, array('async'=>true));
// do other stuff ... 
$stmt = db2_reap_exec($ret1.uid);

it would seem possible to 'rip-the-c-code-guts' out of current language extensions

Is it necessary to rip out? Might not be understanding what "rip" means to you. The async could be optional parm so existing lang driver remains as-is until ready to adopt. In theory one could LIBPATH=/libdb400intercept.a and it wouldn't be disruptive to drivers that only had knowledge of libdb400.a, right? And just to make sure I am understanding flow of execution I've put together the below:

---------------  ---------------  -----------------------  --------------
| script lang |  | script lang |  | libdb400intercept.a |  | libdb400.a |
| adapter     |  | driver      |  |                     |  |            |
---------------  ---------------  -----------------------  --------------

Overall, the approach sounds good at a high level.

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Aaron, thoughts??? questions??? understand???

I think this works for any language 'async', well, +/- copy in/out actual language "variables" monkey business (c-2-c structure manipulation fairly trivial), perhaps try 'quantum' libdb400.a on PHP first (easy language, everyone understands).

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

So, i have been thinking, basically two objectives for a 'async' DB2 'world' ....

1) socket/remote -- xmlservice via HTTP may be the right path here, aka, XML, JSON sort of thing. Everything 'debate' after going with xmlservice seems more about 'speed', which, as you point out, can be manipulated transport (websockets, etc.) and tricks (caching).

2) async libdb400.a -- ODBC/CLI top entry MS link using SQL_STILL_EXECUTING demonstrates expert database view of 'async'. Cool, BUT we/i have already seen low level plumbing alone (CLI libdb400.a), does not fit the desired 'async' outcome (reference API links MySql2 'async' via PHP, Ruby, Python and Node.js). I have coined word 'quantum' for unit-of-async-work (**), where, essentially we want/need an 'async' abstraction API matching scripting languages APIs (see PHP below).

#!php
<?php
// async connect 
$ret = db2_connect($db,$usr,$pwd,array('async'=>'myconn'));
// do other stuff ...
$conn = db2_reap_connect('myconn');
// async query
$ret = db2_exec($conn, $long_running_sql, array('async'=>'mystmt'));
// do other stuff ... 
$stmt = db2_reap_exec('mystmt');
// async fetch ???
$ret = db2_fetch_array($stmt,array('async'=>'myrow'));
// do other stuff ...
$row = db2_reap_fetch('myrow');

// so on ...

Above example is procedural PHP for clarity of what is going on with APIs 'send async' and 'reap async' by 'key'. Other languages supporting callback will have 'hidden' operation (node.js), and, object oriented languages will have object inquires, listener patterns, event patterns, etc. Irrelevant. Ignoring other languages syntax 'hoopla', 'send async' / 'reap async' by 'key' principle is universal.

The punch line ...

With understanding above, it would seem possible to 'rip-the-c-code-guts' out of current language extensions like Ruby ibm_db, and/or PHP ibm_db2, find common working API elements (quantum), then simply add a little multi-thread send/reap magic to provide a expanded libdb400.a 'scripting service' that did all the work in both 'normal' and 'async' modes. These actual language extensions should be very tiny when running on IBM i, aka, just pass the 'operation' to the new libdb400.a 'scripting service'.

A few details (not too many) ...

The 'add-on' libdb400.a, would use same idea as our libdb400.a trace module (sorry readers see Yips service section), place 'super' libdb400.a in front of real libdb400.a and we have added 'scripting service' (LIBPATH=/pathtosuperlibdb400). The new/add-on 'super' libdb400.a essentially is a big old 'pool manger' of connections/statements, wherein, each managed thread will be attached to a QSQ job doing the work. A difference is that we may use many connections same profile to allow 'async' work quantum to spread out across many thread-2-QSQ proxies (*).

(*) Again, reap is find by key, where, key is getting back to the correct statement handle (correct thread's memory, etc.)

(**) quantum -- in any scripting language API (db2_connect, db2_exec, db2_fetch, etc.), multiple actual CLI APIs must be run 'in order' to accomplish the unit-of-work or scripting API. Therein, simply making each CLI API run 'async', wisdom of ODBC/CLI database experts (SQL_STILL_EXECUTING), may not actually help overall goal of the 'composed set of calls' scripting driver operation (extension API). Therefore we need any 'async' operation do all CLI SQL calls to complete the unit-of-work (extension API) ... aka ... 'quantum' of work ... or abbr. 'quantum'.

Well, theory seems ... possible ... mmm ... db2 guys will probably hate it (CLI/ODBC architecture is everything) ... but 'quantum' libdb400.a should work ... welcome to the machine (floyd)???

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Very cool ideas. Few days of back/forth before start code prototype (*).

(*) maybe just do some of these "high speed" things to xmlervice 2 anyway (our other project).

[kadler]

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

---

So, i wonder if we are simply on it all about speed, aka, do same thing xmlservice is doing, but faster??? If true, would more DB2 work in xmlservice be the best option (*)???

Thinking out loud... This would also make it so tier-1 and tier-2 could use the same xmlservice interface without requiring rewrites... with tier-1 you get fast-as-expected with "normal" adapter/driver performance, with tier-2 you get based-on-a-lot-of-things performance.

Hard to say if performance would be "ok" without doing some tests and get some numbers to compare.

Or ... perhaps db2 syntax feel of php ibm_db2 and ruby ibm_db are the thing of beauty, beyond current xmlservice gems/toolkits, aka, parameter I/O values fill into PHP and Ruby vars (assessors, etc.), rows/columns by associative names, so on???

I can't speak for PHP, but with Ruby we wouldn't have to lose the ActiveRecord ORM goodies and instead could write an adapter.

(*) BTW -- argh, yes, less popular xml aside, could be json-xmlservice as being worked next xmlservice version.

What if this implementation of XMLSERVICE operated on a more tightly coupled transport after initial connection/handshake? That's what HTML5's Websockets essentially do - start out as a normal HTTP request and then "upgrade" to more raw TCP/IP (socket) communication where zero HTTP headers/cookies/etc are sent on each request and instead can be as small as beginning byte and an end byte.

OR for that matter, maybe Websockets is a consideration for XMLSERVICE since the latest Apache 2.x for IBM i now supports it. Hmm... THAT would be cool and maybe worth a pursuit outside of this db2sock effort.

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Just so i don't lose the page python async 'quantum' ... python tornado sample

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

More thought ...

Reading back initial readme.md inception of a "good idea".

... create a socket based "database" client ...

This seems a lot like what XMLSERVICE DB2 interface already accomplishes. That is, same sort of things that Ruby and PHP are doing with "async" are already easily done with simple http async requests to xmlservice db2 stuff.

So, i wonder if we are simply on it all about speed, aka, do same thing xmlservice is doing, but faster??? If true, would more DB2 work in xmlservice be the best option (*)???

Or ... perhaps db2 syntax feel of php ibm_db2 and ruby ibm_db are the thing of beauty, beyond current xmlservice gems/toolkits, aka, parameter I/O values fill into PHP and Ruby vars (assessors, etc.), rows/columns by associative names, so on??? If true, would it be heretical to have the PHP, Ruby drivers make REST calls (any type socket) to xmlservice for "async"???

(*) BTW -- argh, yes, less popular xml aside, could be json-xmlservice as being worked next xmlservice version.

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

More clarify 'quantum', ruby mysql2 example ...

#!ruby

client.query("SELECT sleep(5)", :async => true)

# result will be a Mysql2::Result instance
result = client.async_result

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Being a little more specific about 'quantum' ...

PHP query(MYSQLI_ASYNC), followed by reap_async_query, this makes sense to me as a reasonable 'quantum' of work for any script language (callback in nodejs). Whereas low level SQLExecDirect 'async wobble about' until SQL_STILL_EXECUTING is no longer true, seems like a stretch to be valued ...

#!php

$mysqli->query($long_running_sql, MYSQLI_ASYNC);

echo 'run other stuff';

$result = $mysqli->reap_async_query(); //gives result (and blocks script if query is not done)
$resultArray = $result->fetch_assoc();

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

I maybe forgot to make something clear ... DB2 does not support SQL_STILL_EXECUTING (CLI async), this would be an "intercept" at the driver level to "emulate". Aka, client/server operation over a socket (or IPC), wherein DB2 is none the wiser, because we are simply connection pool managing in our "async" low level driver ... if that is what we actually need here ... unclear (thinking phase yet)

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Mmm ... speed up ... perhaps, give/take transfer of data between client/server (unclear).

However, more fundamental questions exist about idea of 'quantum-of-request', aka, at Ruby language level, get metadata what context in an "async" fashion.

To wit, down at libdb400.a level is almost too low, take node.js toolkit DB2 interface example, wherein 'up' at this level "adding" a SQL operation/quantum makes sense for a "async" operation over REST interface.

#!java

function cb(str) {}
var conn1 = new xt.iConn(db, user, pwd, option);
var sql = new xt.iSql();
sql.addQuery("call XMLSERVICE.iHANG()");
conn1.add(sql.toXML());
var conn2 = new xt.iConn(db, user, pwd, option);
var sql1 = new xt.iSql();
XMLSERVICE REST interface ...
sql1.addQuery("call XMLSERVICE.iHANG()");
task 1 and 2 are asynchronous
conn2.add(sql1.toXML());
var task1 = conn1.run(cb); // task 1
var task2 = conn2.run(cb); // task 2

[kadler]

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

---

the less i think we will be able to 'intercept' higher order language functions without major change to the language driver.

Well, if we are the ones also writing the higher order language function then we should be able to gain back the specific-ness we need, correct? For example, are you trying find ways to learn what type of SQL command is being run and that would then tell you whether it would/could be async?

[kadler]

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

---

BTW -- why do all the nothing edits show up??? Can we turn that off???

No way to edit that I can see (I checked).

As you can see in link, wide range of SQL APIs that have been "voted" async good idea (libdb400.a async candidates). They use a SQL_STILL_EXECUTING sort of idea.

Good stuff. I am already imagining how this could speed up ruby-ibm_db given the many calls to get metadata that can be done async.

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

The more i look at these low level SQL APIs (libdb400.a), the less i think we will be able to 'intercept' higher order language functions without major change to the language driver. That is to say, "multiple" c code instructions make up function db.connect (example db2 node.js), as an 'aggregate quantum" we want to be "async" resulting in callback when complete. Therefore, just "async" the actual SQLConnect, may prove a bit useless.

Well, thinking about the problem is required (pause).

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

As you can see in link, wide range of SQL APIs that have been "voted" async good idea (libdb400.a async candidates). They use a SQL_STILL_EXECUTING sort of idea.

BTW -- why do all the nothing edits show up??? Can we turn that off???