XA Datasource: More efficient way to add?

[cristalp]

As a newbie, I've started to define my datasources (I have 30 datasources).

This is a typical example:

                <xa-datasource jndi-name="java:jboss/datasources/dsAuditTrail" pool-name="AuditTrailPool" enabled="true" spy="false" use-ccm="true" statistics-enabled="true">
                    <xa-datasource-property name="URL">
                        jdbc:sqlserver://foobar:1433;databaseName=audit_trail
                    </xa-datasource-property>
                    <xa-datasource-property name="User">
                        audit_trail_user01
                    </xa-datasource-property>
                    <xa-datasource-property name="Password">
                        change_the_password
                    </xa-datasource-property>
                    <xa-datasource-property name="SelectMethod">
                        cursor
                    </xa-datasource-property>
                    <xa-datasource-property name="ApplicationIntent">
                        ReadWrite
                    </xa-datasource-property>
                    <xa-datasource-property name="ApplicationName">
                        JBoss-AuditTrailPool
                    </xa-datasource-property>
                    <driver>sqlserver</driver>
                    <xa-pool>
                        <min-pool-size>5</min-pool-size>
                        <max-pool-size>64</max-pool-size>
                        <prefill>false</prefill>
                        <is-same-rm-override>false</is-same-rm-override>
                        <no-tx-separate-pools>true</no-tx-separate-pools>
                        <pad-xid>true</pad-xid>
                    </xa-pool>
                    <recovery>
                        <recover-credential>
                            <user-name>audit_trail_user01</user-name>
                            <password>change_the_password</password>
                        </recover-credential>
                    </recovery>
                    <validation>
                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
                        <validate-on-match>false</validate-on-match>
                        <background-validation>true</background-validation>
                        <background-validation-millis>5000</background-validation-millis>
                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter"/>
                    </validation>
                    <statement>
                        <track-statements>true</track-statements>
                        <prepared-statement-cache-size>5000</prepared-statement-cache-size>
                    </statement>
                </xa-datasource>

If I add it using JBoss CLI, I do the following:

xa-data-source add --name=AuditTrailPool --jndi-name=java:jboss/datasources/dsAuditTrail \
  --spy=false --use-ccm=true --statistics-enabled=true --driver-name=sqlserver \
  --xa-datasource-properties={ \
    "URL" => "jdbc:sqlserver://foobar:1433;databaseName=audit_trail", \
    "User" => "audit_trail_user01", \
    "Password" => "change_the_password", \
    "SelectMethod" => "cursor", \
    "ApplicationIntent" => "ReadWrite", \
    "ApplicationName" => "JBoss-AuditTrailPool" } \
  --recovery-username=audit_trail_user01 --recovery-password=change_the_password \
  --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker \
  --validate-on-match=false --background-validation=true \
  --background-validation-millis=5000 \
  --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter \
  --track-statements=true --prepared-statements-cache-size=5000 \
  --min-pool-size=5 --max-pool-size=64 --pool-prefill=false \
  --same-rm-override=false --pad-xid=true --no-tx-separate-pool=true \
  --enabled=true

This happens instantaneously, and then I :reload .

Now, the same example with just the basic configuration looks like this for Jcliff (if I'm doing it right):

{ "xadatasource" => {
    "AuditTrailPool" => {
      "jndi-name" => "java:jboss/datasources/dsAuditTrail",
      "pool-name" => "AuditTrailPool",
      "spy" => "false",
      "use-ccm" => "true",
      "statistics-enabled" => "true",
      "driver-name" => "sqlserver",
      "xa-datasource-properties" => {
        "URL" => { "value" => "jdbc:sqlserver://foobar:1433;databaseName=audit_trail" },
        "User" => { "value" => "audit_trail_user01" },
        "Password" => { "value" => "change_the_password" },
        "SelectMethod" => { "value" => "cursor" },
        "ApplicationIntent" => { "value" => "ReadWrite" },
        "ApplicationName" => { "value" => "JBoss-AuditTrailPool" }
      }
    }
  }
}

I measure the time this takes, and it takes 4 1/2 minutes:

time jcliff datasource.jcliff
Jcliff version 2.12.7
2021-02-17 13:56:44:0255: /subsystem=datasources:read-children-resources(child-type=xa-data-source,recursive=true)
2021-02-17 13:56:46:0241: xa-data-source add --name=AuditTrailPool --jndi-name="java:jboss/datasources/dsAuditTrail" --driver-name="sqlserver" --xa-datasource-properties=URL="jdbc:sqlserver://foobar:1433;databaseName=audit_trail"
if result.value==false of /subsystem=datasources/xa-datasource=AuditTrailPool:read-resource
xa-data-source enable --name=AuditTrailPool
:reload
2021-02-17 13:57:10:0094: /subsystem=datasources:read-children-resources(child-type=xa-data-source,recursive=true)
2021-02-17 13:57:11:0996: /subsystem=datasources/xa-data-source=AuditTrailPool:write-attribute(name=statistics-enabled,value="true")
xa-data-source disable --name=xadatasource
:reload
/subsystem=datasources/xa-data-source=AuditTrailPool/xa-datasource-properties=User:add(value="audit_trail_user01")
:reload
xa-data-source enable --name=xadatasource
xa-data-source disable --name=xadatasource
:reload
/subsystem=datasources/xa-data-source=AuditTrailPool/xa-datasource-properties=Password:add(value="change_the_password")
:reload
xa-data-source enable --name=xadatasource
xa-data-source disable --name=xadatasource
:reload
/subsystem=datasources/xa-data-source=AuditTrailPool/xa-datasource-properties=SelectMethod:add(value="cursor")
:reload
xa-data-source enable --name=xadatasource
xa-data-source disable --name=xadatasource
:reload
/subsystem=datasources/xa-data-source=AuditTrailPool/xa-datasource-properties=ApplicationIntent:add(value="ReadWrite")
:reload
xa-data-source enable --name=xadatasource
xa-data-source disable --name=xadatasource
:reload
/subsystem=datasources/xa-data-source=AuditTrailPool/xa-datasource-properties=ApplicationName:add(value="JBoss-AuditTrailPool")
:reload
xa-data-source enable --name=xadatasource

real    4m29.473s
user    1m49.782s
sys     0m6.752s

Of course, I understand that idempotency is Jcliff's forte. But waiting two hours just to add a couple of datasources is too long for my use case.

So, I have two questions:

• Am I doing this right in Jcliff?
• Is there a way where the idempotency could only consist of removing the datasource and adding it in the way shown above (my CLI example)?

[bserdar]

@cristalp The default wait for "reload" is 20secs. Give a different value using --reconnect-delay flag. It is in msecs.

I will look at grouping reloads together.

[cristalp]

Thanks for the configuration hint!