How to make a query to run on a calling thread?

[capoorhimanshu]

Hi ,

I do not want to use engine's threads to run the query in some cases. What i want when user  set some property , query should get run in a calling thread rather than in another thread which is actually happening. I found that their is one property useCallingThread. I set this property to true and still query is getting executed in separate thread. I also tried to used the local connection using embedded profile but still query is getting executed in separate thread.

Is there is any way to make the query to run in a calling thread ?

Thanks

Himanshu Kapoor

[shawkins]

> I also tried to used the local connection using embedded profile but still query is getting executed in separate thread.

That is a prerequisite.  You cannot use the calling thread with a socket connection.  The default for useCallingThread is true, so it should default to the behavior that you want.

Do you have a tread dump showing unexpected behavior?  And are you doing anything odd like using the client connection in a multithreaded manner?

[capoorhimanshu]

Hi Steve ,

Thanks for the reply. What i am trying to do is i created a local connection using embedded profile , similarly the way its been done in embedded server class

private EmbeddedProfile embeddedProfile = new EmbeddedProfile() {

   @Override
   public ConnectionImpl connect(String url, Properties info)

   throws TeiidSQLException {

  ServerConnection conn;

   try {

  conn = createServerConnection(info);

  } catch (TeiidException e) {

   throw TeiidSQLException.create(e);

  }

   return new EmbeddedConnectionImpl(conn, info, url);

  }

   @Override
   public ServerConnection createServerConnection(Properties info)

   throws TeiidException {

  LocalServerConnection conn = new LocalServerConnection(info, useCallingThread) {

   @Override
   protected ClientServiceRegistry getClientServiceRegistry(String name) {

   return services;

  }

   @Override
   public void addListener(VDBLifeCycleListener listener) {

  EmbeddedServer.this.repo.addListener(listener);

  }

   @Override
   public void removeListener(VDBLifeCycleListener listener) {

  EmbeddedServer.this.repo.removeListener(listener);

  }

  };

  conn.getWorkContext().setConnectionProfile(this);

   return conn;

  }

};

private final class EmbeddedConnectionImpl extends ConnectionImpl implements EmbeddedConnection {

   public EmbeddedConnectionImpl(ServerConnection serverConn,

  Properties info, String url) {

   super(serverConn, info, url);

  }

   @Override
   public CallableStatement prepareCall(Command command, EmbeddedRequestOptions options)

   throws SQLException {

  CallableStatementImpl csi = this.prepareCall(command.toString(), options.getResultSetType(), ResultSet.CONCUR_READ_ONLY);

  csi.setCommand(command);

   return csi;

  }

   @Override
   public TeiidPreparedStatement prepareStatement(Command command,

  EmbeddedRequestOptions options) throws SQLException {

  PreparedStatementImpl psi = this.prepareStatement(command.toString(), options.getResultSetType(), ResultSet.CONCUR_READ_ONLY);

  psi.setCommand(command);

   return psi;

  }

}

and then tried to do like this :

Connection connection =  embeddedProfile.connect(url,info);

Statement statement = connection.createStatement();

ResultSet resultSet = statement.executeQuery(sql)

i tried to print the thread ids but when execute query call comes to my connector to ask for connection , thread id differs from the calling thread . Am i missing something here ?

I did not get this: And are you doing anything odd like using the client connection in a multithreaded manner?  Can you please elaborate.

Thanks

Himanshu Kapoor

[rareddy]

To have the same thread in the connector too you need to set "thread-count-for-source-concurrency" property, read from Threading - Teiid 8.11 (draft) - Project Documentation Editor you need to set this property in DQPCore.

[rareddy]

That would be "EmbeddedConfiguration.setUserRequestSourceConcurrency" method

[capoorhimanshu]

Hi Ramesh ,

Thanks . I will check this. But if i set this property in DQPCore i believe all the queries on source will run serially. However what i am looking actually is :

I have deployed the VDB and opened the port to make the JDBC connections on it, so in case user comes from this route i want to make the queries keep running asynchronously , but at the same time i have exposed a web service to fetch the preview of the result set of the VDS. When user calls my web service i want to make a local connection on top of VDB instead of going through sockets and want this query run in a same calling thread.

Thanks

Himanshu Kapoor

[rareddy]

The execution at connector level is separate concern, you need to decide you want to execute serially or in parallel at connector level. If you choose serial then Teiid can use same thread all the way, if not it will spawn another thread for each connector.

[capoorhimanshu]

Hi ,

Does that mean all the queries can be executed either serially or either in parallel ?

I want to give two interfaces to the end user to run the query:

1 ) jdbc - when they make a socket connection on top of VDB , using JDBC driver . In this case i want to use thread from the pool and want the requests to be executed in parallel . This is working fine.

2) Web service -- when they come through web service , i want to create a local connection on top of VDB and want queries to run in calling thread itself rather then using a thread from the pool/separate thread. I am building a embedded connection here but still the requests gets executed in separate thread and not in the calling one , where as useCallingThread is true by default in this case .

Would it be possible or is it either i can set all to serial or all to in parallel

Thanks

Himanshu Kapoor

[rareddy]

You could have two separate embedded engines one for each, but not with current design.

Let's look at the problem in a different way, what is issue you are concerned with multiple threads at connector level?

[shawkins]

Just to make sure, you should not need to create your own embeddedprofile.  Is there a reason you were doing that?

> Does that mean all the queries can be executed either serially or either in parallel ?

All source queries under a user query can run in the engine/calling thread with UserRequestSourceConcurrency = 1 or for values greater than 1 with up to n concurrent threads.

> 1 ) jdbc - when they make a socket connection on top of VDB , using JDBC driver . In this case i want to use thread from the pool and want the requests to be executed in parallel . This is working fine.

An overview of the the execution flow is that an nio worker thread will perform the io work and pass the message off to the engine to be processed.  The engine will use a thread from the engine thread pool to begin work.  For a non-transactional query based upon the UserRequestSourceConcurrency and a source that is forkable, a connector work item will be started in another engine thread.  The plan will then detach processing thread if it cannot get results from the source accesses and allow that thread to perform other work.  Once results are delivered from the connector work the plan will be processed further.

> 2) Web service -- when they come through web service , i want to create a local connection on top of VDB and want queries to run in calling thread itself rather then using a thread from the pool/separate thread. I am building a embedded connection here but still the requests gets executed in separate thread and not in the calling one , where as useCallingThread is true by default in this case .

You have to clarify this a little more to differentiate between the user request / plan and the source queries / connector work.  With a local connection and the useCallingThread true, then the same execution flow is used as above except that the role of plan processing will only be performed by calling thread - connector work can still be separate threads.

[capoorhimanshu]

Thanks Steve.

What i was looking for was : a connection level property , if say that is set to true then all queries on connection should run on single thread and if set to false then all the queries on that connection  should execute on different thread by using the thread from the thread pool. And i assumed useCallingThread is the property for that. However i understand from your reply by setting this property plan processing will be done in same thread and connector work can still work on different thread.

Seems as of now i can only achieve it on global level using UserRequestSourceConcurrency.

[shawkins]

Is there a reason why you want to serialize the work coming in from a web service?  The engine processing will already be done on the calling thread.  Is there a reason the source access should be as well?

[capoorhimanshu]

Actually i wanted to provide an interface on web UI to provide the preview functionality to the user where user can write a query and top 50 records will be returned.  In my application underneath source connections are secured one and requires authorized user to be logged in the application before they can access any secured resource. In my application user can access vdb in two ways one is actual JDBC connection and other is preview where few records are returned over a web service call.

In case of a JDBC connection on top of VDB we expect authorized user credentials to be passed in JDBC connection properties , using those credentials I make sure proper security context is set in worker threads so that those threads can access the underneath sources in case rights given to that user. But for web services security contexts are set at initial login call and session is created and further web service call on that session do not have access to security context as well to credentials, so in short when user clicks on web ui for preview i do not have credentials as well as security context which i can set on worker threads , hence when worker threads goes to access the underneath sources , security exceptions are raised.

Also for preview stuff i wanted to keep the user interface simple where after deploying the VDB, user can test/preview the data by simply firing the query rather than providing all the JDBC properties like user name / password on UI. So in this case i was planning to create the embedded connection internally and was thinking that if somehow if i can make connector worker to run in a single thread i.e. (preview call which already have security context set in) so that can access the under neath sources.

Thanks

Himanshu Kapoor

[shawkins]

> But for web services security contexts are set at initial login call and session is created and further web service call on that session do not have access to security context as well to credentials, so in short when user clicks on web ui for preview i do not have credentials as well as security context which i can set on worker threads , hence when worker threads goes to access the underneath sources , security exceptions are raised.

If your scenario works with a single thread, then it should be able to work with connector threads as well.  For example, Teiid propagates the JAAS security context when executing connector tasks.  How are you determining the credentials at the source layer?