MQSeries.net :: View topic - mqsireportproperties output in XML format?

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » mqsireportproperties output in XML format?

mqsireportproperties output in XML format?

« View previous topic :: View next topic »

Author

Message

t603

Posted: Tue Oct 15, 2013 1:14 pm Post subject: mqsireportproperties output in XML format?

Voyager

Joined: 16 Oct 2012
Posts: 88
Location: Prague, the Czech Republic, Europe

Good evening,

can I ask You, if there is any ready to made way, tool, switch, etc., which enable to get output (or the same information) from mqsireportproperties command in XML format? In WBM 7.0.0.n (or newer for the future).

I want to compare as many as possible configuration items of my 5 slightly different brokers among themselves and also each broker in time line via some script using XSL-T, XML Diff... Current output format of mqsireportproperties is hard to use for it (at least for me), since it is just structured text with ESQL inside the output.

I found just only way how to achieve it - using Java API. OK, but I would like to verify, if there is some already made solution for this or not. There are many useful tools outside WMB like QLOAD and others, so maybe...

Thank You in advance for answers. Stepan

smdavies99

Posted: Tue Oct 15, 2013 9:51 pm Post subject:

Jedi Council

Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.

great idea. I don't know of any tool apart from some custom ones that might do it.

However, I think you are only going part of the way.

Wouldn't it be nice to use an XML file to actually create the broker and all the bits.
Imagine that you create a spreadsheet with all the config details and press export as XML then feed that into a command/shell script/app that does all the heavy lifting for you.

I'd vote for that one if it was an RFE

_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995

Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions.

mqjeff

Posted: Wed Oct 16, 2013 1:49 am Post subject:

Grand Master

Joined: 25 Jun 2008
Posts: 17447

All configuration of runtimes should be done using scripts.

Anything you want to get from mqsireportproperties you can also get from an CMP/Admin/Broker/Integration API application - whatever it's called for the version you're using.

XML is great. Except when it's not.

smdavies99

Posted: Wed Oct 16, 2013 2:06 am Post subject:

Jedi Council

Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.

mqjeff,
IMHO, you are only partially right.

I'd really like a script/tool that could extract everything from a broker config and put it into a form that could be edited and then used to build a new broker.

Think of it like dmpmqcfg but for broker. The output of dmpmqcfg is editable and replayable and therefore can be used to create a new QM that is based upon the data extracted from another QM.

Having something like that for broker would be IHMO (not that it counts for anything) very useful.
I suggested XML but it could equally be csv and that data could be edited in tools like a spreadsheet.

also getting a complete dump of a broker config in a form that is easy to use (executing a myriad of slightly different mqsireportproperites commands) is not easy to use IMHO would be very useful for audit purposes.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995

Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions.

t603

Posted: Wed Oct 16, 2013 2:28 am Post subject:

Voyager

Joined: 16 Oct 2012
Posts: 88
Location: Prague, the Czech Republic, Europe

Quote:

All configuration of runtimes should be done using scripts.

Yes, I never said nothing against it.

But:

1) Dump of configuration to the current text format is (at least for me) almost unusable for automatic analysis.

2) I guess, that XML is often used for configuration purposes today.

3) Script could use XML as input and output.

4) One can easily pick up requested information from XML dump of mqsireportproperties using XSL-T, but very hard or uncertain or impossible from current text format (due ESQL in dump).

The purpose of my ask/request is to manage 5 brokers with about 1.500 message flows all of them developed by external internal 20+ developers. Now I accepted new broker into production and i am looking for the way, how to ensure, that new one broker is the same in base as other ones, that message flow on broker A has the same setting (like instances) as the same flow on other brokers...

All I want to have is something like BAR (= XML) for configuration, at least for reading => mqsireportproperties.

mqjeff

Posted: Wed Oct 16, 2013 2:32 am Post subject:

Grand Master

Joined: 25 Jun 2008
Posts: 17447

I guess I've not bothered to check out how human readable/machine consumable the output of mqsibackupbroker is.

I don't think I actually disagreed with the premise behind the question, I just suggested that it's better to be able to positively assert the configuration of a broker, rather than having to discover it.

That is, to say "I know these two brokers are exactly alike because I have audit records of all of the scripts that were used to configure them", rather than saying "I know these two brokers are exactly alike because I discovered that they have all the same settings".

Again, all of the information you can get from mqsireportproperties you should be able to get from an AdminAPI application, which can very easily output data in any form you want.

Yes, I agree it would be cool if the webadmin console had a function for comparing the configuration of two brokers.

t603

Posted: Wed Oct 16, 2013 4:23 am Post subject:

Voyager

Joined: 16 Oct 2012
Posts: 88
Location: Prague, the Czech Republic, Europe

Quote:

That is, to say "I know these two brokers are exactly alike because I have audit records of all of the scripts that were used to configure them", rather than saying "I know these two brokers are exactly alike because I discovered that they have all the same settings".

I am sorry for my English, sometimes I do not catch meaning of the sentence, especially when there are used phrasal verbs or something like this.

But - yes, Your approach is the best for ideal world - to have under control all configuration data and flows, and message sets... deployed to the Broker. But from my experience I do not have this under control and after different ways to achieve this goal, I can not rely on it. One small example - number of additional instances of flows can be deployed by different ways depend on regular development, hotfix using broker.xml in BAR or mouse clicking in Explorer or using command. And the last two ways I do not have under control.

I need to compare the result of all deployments ex post. From many reasons. And the output of mqsireport* to XML would help me a lot.

Meanwhile I started to develop my Java app using API to Broker to get these info in XML ~ God Save our Broker instances.

PeterPotkay

Posted: Wed Oct 16, 2013 4:32 am Post subject:

Poobah

Joined: 15 May 2001
Posts: 7722

MS03 for WMB is long overdue.
_________________
Peter Potkay
Keep Calm and MQ On

mqjeff

Posted: Wed Oct 16, 2013 4:33 am Post subject:

Grand Master

Joined: 25 Jun 2008
Posts: 17447

Yes, sometimes my English is rather too florid, and not properly concise and American. That is to say, sometimes it's too English.

I agree with everything you've said.

I'm saying that there are options to get the information you need, in the format you want.

I'm also saying that the format you want may not be the best solution for everyone.

I'm also saying that it's better to solve this problem in other ways.

I do understand that the real world is not perfect nor ideal, and that we often have to discover things that should have been properly documented.

This conversation leaves me wondering how difficult it would be to create a JAXB wrapper around the Admin API objects. I don't know enough about building JAXB classes to know if it's easy to ask something to generate a binding from an existing set of classes.

t603

Posted: Wed Oct 16, 2013 5:05 am Post subject:

Voyager

Joined: 16 Oct 2012
Posts: 88
Location: Prague, the Czech Republic, Europe

I filled in RFE on this topic.

http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=40438

We will see, how many persons would like to have such feature.

t603

Posted: Fri Feb 21, 2014 10:36 am Post subject:

Voyager

Joined: 16 Oct 2012
Posts: 88
Location: Prague, the Czech Republic, Europe

New version of the parser of the text output of mqsireportproperties to XML and SQL for the purpose of computer based analysis as at 2014-05-14.

What is new:
1) Fixed several serious bugs (non well formed XML output).
2) Added processing of Attribute="something" to the XML output.
3) Fixed unexpected EOF in awk script on MS Windows.
4) Significantly simplified whole processing to just only one AWK skript (TXT -> XML) and one XSL-T 2.0 script (XML -> SQL).
5) Speed improvement.
6) Significantly better documentation of AWK script (added comments).
7) Better readability and manageability of AWK code (repeating code extracted to user functions).

Any improvements are greatly appreciated!

Many thanks to IBM developer, who wroted mqsiReportProperties output so nice strictly intended, that I was able to parse it without hardcoded exceptions!

Developed and tested using Windows 7 64 bit, GNU Awk 3.1.0 32 bit, Java 1.7.0_51 64 bit, Saxon-HE 9.5.1.1J.

_run.bat

Code:

cls
:mqsireportproperties
mqsireportproperties BK -e EG -o AllMessageFlows -r -p /tmp/BK.EG.AllMessageFlows.txt
rem Call mqsireportproperties as many times as You want.
:program
rem
del /q .\data\%1\*AllMessageFlows.xml
del /q .\data\%1\*AllMessageFlows.sql
del /q .\data\%1\in.sql
:txt2xml
rem GNU awk is a must!
for %%f in (.\data\%1\*.txt) do .\bin\gawk -v BINMODE=r -f .\script\script_a.awk %%f > %%~df%%~pf%%~nf.xml
:xml2sql
rem use Saxon HE
for %%f in (.\data\%1\*.xml) do java -Xmx4096m -jar .\bin\saxon9he.jar -t -s:%%f -xsl:.\script\xml2sql.xsl -o:%%~df%%~pf%%~nf.sql
:xml2MessageFlowPerFile
for %%f in (.\data\%1\*.xml) do java -Xmx4096m -jar .\bin\saxon9he.jar -t -s:%%f -xsl:.\script\xml2MessageFlowPerFile.xsl -o:%%~df%%~pf%%~nf.xml
rem
type .\data\%1\*.sql > .\data\%1\in.sql
rem
del /q .\data\%1\*AllMessageFlows.txt
del /q .\data\%1\*AllMessageFlows.xml
del /q .\data\%1\*AllMessageFlows.sql
:end

script_a.awk

Code:

# use GNU awk

# tested on 5 brokers ~ 89 execution groups ~ 1240 message flows

# close the last multiline attribute, if it is open
function closeTheLastMultilineAttribute()
{
if (isTheLastAttributeMultiline == "yes") {
print "";
isTheLastAttributeMultiline = "no";
}# end if
}# end function

# handle closing previous element
function handleClosingPreviousElement()
{
# test, if the actual line is the XML following-sibling of the previous line
if (actualElementDepth == previousElementDepth) {
# close previuos-sibling
print "";
}# end if

# test, if the actual line is the XML child of the previuos line
if (actualElementDepth < previousElementDepth) {
# close all children of previuos sibling
for(i = 0; i < (previousElementDepth - actualElementDepth); i++ ) {
print "";
}# end for
print "";
}# end if

# test, if the actual line is the XML parent of the previous line
if (actualElementDepth > previousElementDepth) {
# do nothing
}# end if
}# end function

# processig the beginning of the file
BEGIN {

FS = ""; # input field separator - none, no CSV, just plain source text
RS = "\n"; # input record separator
OFS = ""; # output field separator - none, no CSV, just XML
ORS = ""; # output record separator - none, I want to beautify XML by myself

previousElementDepth = 0; # depth of intendation of IBM's built in message flow node elements
actualElementDepth = 0; # depth of intendation of IBM's built in message flow node elements

# which encoding produce mqsireportproperties? I wish utf-8, but...
print "<?xml version=\"1.0\" encoding=\"iso-8859-1\"?>";
# opening root element
print "\n" "<r>";
# the first DummyElement to initiate elementDepths and starting 
print "\n" "";

}# end BEGIN

# processig each line of the file
{

# replace some non printable characters
gsub(/\x1A/, "?NPC?", $0);

# replace XML special character &
gsub(/&/, "\\&", $0);

# replace XML special character "
gsub(/"/, "\\"", $0);

# replace XML special character <
gsub(/</, "\\<", $0);

# replace XML special character >
gsub(/>/, "\\>", $0);

# is it the start of the new singleline attribute?
if ($0 ~ /^[[:blank:]]*[a-zA-Z0-9]*='.*'$/ && $0 !~ /^[[:blank:]]*(computeExpression|filterExpression|statement|longDesc|pattern|Attribute)='.*/) {

# INLINE ATTRIBUTE

# close the last multiline attribute, if it is open
closeTheLastMultilineAttribute();

# create the new ccc
# and replace attribute text delimiters from ' to "
print "\n" "" gensub(/^[[:blank:]]*[a-zA-Z0-9]*='(.*)'$/, "\\1", $0) "";

# is it the start of the new element?
} else if ($0 ~ /^[[:blank:]]*(Connection|Attribute|ComIbmComputeNode|ComIbmConfigurationNode|ComIbmDatabaseNode|ComIbmFilterNode|ComIbmFlowOrderNode|ComIbmLabelNode|ComIbmMQGetNode|ComIbmMQHeaderNode|ComIbmMQInputNode|ComIbmMQOutputNode|ComIbmPassthruNode|ComIbmResetContentDescriptorNode|ComIbmRouteNode|ComIbmRouteToLabelNode|ComIbmTimeoutNotificationNode|ComIbmTryCatchNode|Connection|EventSource|InputTerminal|LogicalConnection|MbTable|MessageFlow|OutputTerminal|Parser|Terminal|Row|ComIbmSOAPInputNode|ComIbmValidateNode|ComIbmSOAPExtractNode|ComIbmFileOutputNode|ComIbmHTTPHeaderNode|ComIbmWSRequestNode|ComIbmSOAPReplyNode|ComIbmSOAPEnvelopeNode|ComIbmFileInputNode|ComIbmTraceNode|SNMPTrapGenNode|ComIbmCheckNode|ComIbmTimeoutControlNode|ComIbmWSInputNode|ComIbmSOAPRequestNode|ComIbmWSReplyNode|ComIbmThrowNode|ComIbmEmailOutputNode|ComIbmPSServiceNode|ComIbmAggregateControlNode|ComIbmAggregateRequestNode|ComIbmAggregateReplyNode|ComIbmCollectorNode).*/) {
# above are list of IBM's built in message flow node elements used in CSOB

# ELEMENT

# close the last multiline attribute, if it is open
closeTheLastMultilineAttribute();

# count and save the actual element depth
actualElementDepth = (match($0, /[^[:blank:]]/) - 1) / 2; # indentation of mqsireportproperties output is 2 spaces

# handle closing previous element
handleClosingPreviousElement();

# create the new 
print "\n" "";

# exception for: Attribute='[A-Z]*', IBM's strange output formatting for Attribute element
if ($0 ~ /^([[:blank:]]*Attribute.*)/) {
print "\n" "" gensub(/^[[:blank:]]*Attribute='(.*)'/, "\\1", $0) "";
}# end if

previousElementDepth = actualElementDepth;

} else {

# MULTILINE attribute

# create the new 
if ($0 ~ /^[[:blank:]]*(computeExpression|filterExpression|statement|longDesc|pattern)='.*/) {

# close the last multiline attribute, if it is open
closeTheLastMultilineAttribute();

# create the new 
print "\n" "" gensub(/^[[:blank:]]*(computeExpression|filterExpression|statement|longDesc|pattern)=('.*)/, "\\2", $0);
isTheLastAttributeMultiline = "yes";

} else {

# continuation of the multiline attribute
print "\n" $0;

}# end if-else

}# end if-else

}# end if

# processig the end of the file
END {

# close last multiline attribute, if it is open
closeTheLastMultilineAttribute();

# count and save actual element depth
actualElementDepth = 0

# handle closing previous element
handleClosingPreviousElement();

# closing root element
print "</r>";

}# end END

xml2sql.xsl

Code:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" exclude-result-prefixes="xs" version="2.0">

<xsl:output method="text"/>
<xsl:strip-space elements="*"/>



<xsl:variable name="sqm">'</xsl:variable>

<xsl:variable name="dsqm">''</xsl:variable>

<xsl:variable name="nl">
</xsl:variable>

<xsl:variable name="fileName" select="replace(base-uri(), '.*/(.*)', '$1')"/>

<xsl:variable name="brokerName" select="replace($fileName, 'sry\.(.*)\.(.*)\.AllMessageFlows\.xml', '$1')"/>

<xsl:variable name="executionGroupName" select="replace($fileName, 'sry\.(.*)\.(.*)\.AllMessageFlows\.xml', '$2')"/>


<xsl:template match="/root">
<xsl:apply-templates>

<xsl:with-param name="messageFlowName" select="''"/>
<xsl:with-param name="nodePid" select="'d1e1'"/>
<xsl:with-param name="nodeXPath" select="'/'"/>
<xsl:with-param name="nodeFullXPath" select="'/'"/>

</xsl:apply-templates>
</xsl:template>

<xsl:template match="i">

<xsl:param name="messageFlowName"/>
<xsl:param name="nodePid"/>
<xsl:param name="nodeXPath"/>
<xsl:param name="nodeFullXPath"/>


<xsl:variable name="l_messageFlowName">
<xsl:choose>
<xsl:when test="./@t = 'MessageFlow'">
<xsl:value-of select="./i[@t = 'label']/text()"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$messageFlowName"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:variable name="l_nodeId" select="generate-id(.)"/>
<xsl:variable name="l_nodePid" select="$nodePid"/>
<xsl:variable name="l_nodeXPath">

<xsl:choose>
<xsl:when test="@t != 'MessageFlow'">
<xsl:value-of select="concat($nodeXPath, '/', replace(./@t, $sqm, $dsqm), '[', position(), ']')"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="concat($nodeXPath, '/', replace(./@t, $sqm, $dsqm), '[*]')"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:variable name="l_nodeFullXPath">
<xsl:choose>
<xsl:when test="boolean(./child::i[@t = 'uuid']) or boolean(./child::i[@t = 'label'])">
<xsl:choose>
<xsl:when test="@t != 'MessageFlow'">
<xsl:value-of select="concat($nodeFullXPath, '/', replace(./@t, $sqm, $dsqm), '[@position="', position(), '", @uuid="', replace(./child::i[@t = 'uuid'], $sqm, $dsqm), '", @label="', replace(./child::i[@t = 'label'], $sqm, $dsqm), '"]' )"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="concat($nodeFullXPath, '/', replace(./@t, $sqm, $dsqm), '[@position="null", @uuid="', 'null', '", @label="', replace(./child::i[@t = 'label'], $sqm, $dsqm), '"]' )"/>
</xsl:otherwise>
</xsl:choose>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="concat($nodeFullXPath, '/', replace(./@t, $sqm, $dsqm), '[@position="', position(), '"]')"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:variable name="l_nodeValueLength" select="string-length(./text())"/>
<xsl:variable name="l_nodeValueVarchar">
<xsl:choose>
<xsl:when test="$l_nodeValueLength <= 1024">
<xsl:value-of select="./text()"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="null"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:variable name="l_nodeValueClob">
<xsl:choose>
<xsl:when test="$l_nodeValueLength > 1024">
<xsl:value-of select="./text()"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="null"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>

<xsl:value-of select="
concat(
'
',
$sqm,
$brokerName,
$sqm,
', ',
$sqm,
$executionGroupName,
$sqm,
', ',
$sqm,
$l_messageFlowName,
$sqm,
', ',
$sqm,
$l_nodeId,
$sqm,
', ',
$sqm,
$l_nodePid,
$sqm,
', ',
$sqm,
@c,
$sqm,
', ',
$sqm,
@l,
$sqm,
', ',
$sqm,
$l_nodeXPath,
$sqm,
', ',
$sqm,
$l_nodeFullXPath,
$sqm,
', ',
$sqm,
@t,
$sqm,
', ',
$sqm,
replace($l_nodeValueVarchar, $sqm, $dsqm),
$sqm,
', ',
$sqm,
replace($l_nodeValueClob, $sqm, $dsqm),
$sqm
)
"/>
<xsl:apply-templates>

<xsl:with-param name="messageFlowName" select="$l_messageFlowName"/>
<xsl:with-param name="nodePid" select="$l_nodeId"/>
<xsl:with-param name="nodeXPath" select="$l_nodeXPath"/>
<xsl:with-param name="nodeFullXPath" select="$l_nodeFullXPath"/>

</xsl:apply-templates>
</xsl:template>

<xsl:template match="text()"/>

</xsl:stylesheet>

createDatabaseObjects.sql

Code:

--this script is for H2 database

set LOG = 0; --(disabling the transaction log)
set CACHE_SIZE = 262144; --(a large cache is faster)
set LOCK_MODE = 0; --(disable locking)
set UNDO_LOG = 0;

select * from INFORMATION_SCHEMA.SETTINGS;

drop index if exists it01;
drop index if exists it02;
drop index if exists it03;
drop index if exists it04;
drop index if exists it05;
drop index if exists it06;
drop index if exists it07;
drop index if exists it08;
drop index if exists it09;
drop index if exists it10;

drop table if exists t;
create table if not exists t (
id identity,
brokerName varchar(16), -- max 8, 16 should be enough
executionGroupName varchar(32), -- max 20, 32 should be enough
messageFlowName varchar(256), -- max 120, 256 should be enough
nodeId varchar(16), -- max 10, 16 should be enough
nodePid varchar(16), -- max 10, 16 should be enough
intendation tinyint, -- max 4, tinyint (127) should be enough
xmlLineNumber integer, -- max 2092045, integer (2147483647) should be enough
nodeXPath varchar(128), -- max 88, 128 should be enough
nodeFullXPath varchar(2048), -- max 1231, 2048 should be enough
nodeName varchar(64), -- max 32, 64 should be enough
nodeValueSha256 char(128),
nodeValueVarchar varchar(1024), --
nodeValueClob clob -- CLOB, extra long for ESQLs
);

--truncate table t;

insert into t (brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, nodeValueSha256, nodeValueVarchar, nodeValueClob) select brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, hash('SHA256', stringtoutf8(coalesce(nodeValueVarchar, nodeValueClob)), 2), nodeValueVarchar, nodeValueClob from csvread ('C:\temp\temp\mqsiReportPropertiesToXmlAndSql\data\BK1\in.sql', 'brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, nodeValueVarchar, nodeValueClob', 'fieldDelimiter='' fieldSeparator=, escape=''');
insert into t (brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, nodeValueSha256, nodeValueVarchar, nodeValueClob) select brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, hash('SHA256', stringtoutf8(coalesce(nodeValueVarchar, nodeValueClob)), 2), nodeValueVarchar, nodeValueClob from csvread ('C:\temp\temp\mqsiReportPropertiesToXmlAndSql\data\BK2\in.sql', 'brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, nodeValueVarchar, nodeValueClob', 'fieldDelimiter='' fieldSeparator=, escape=''');
insert into t (brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, nodeValueSha256, nodeValueVarchar, nodeValueClob) select brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, hash('SHA256', stringtoutf8(coalesce(nodeValueVarchar, nodeValueClob)), 2), nodeValueVarchar, nodeValueClob from csvread ('C:\temp\temp\mqsiReportPropertiesToXmlAndSql\data\BKSP\in.sql', 'brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, nodeValueVarchar, nodeValueClob', 'fieldDelimiter='' fieldSeparator=, escape=''');
insert into t (brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, nodeValueSha256, nodeValueVarchar, nodeValueClob) select brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, hash('SHA256', stringtoutf8(coalesce(nodeValueVarchar, nodeValueClob)), 2), nodeValueVarchar, nodeValueClob from csvread ('C:\temp\temp\mqsiReportPropertiesToXmlAndSql\data\CML1A.BK\in.sql', 'brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, nodeValueVarchar, nodeValueClob', 'fieldDelimiter='' fieldSeparator=, escape=''');
insert into t (brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, nodeValueSha256, nodeValueVarchar, nodeValueClob) select brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, hash('SHA256', stringtoutf8(coalesce(nodeValueVarchar, nodeValueClob)), 2), nodeValueVarchar, nodeValueClob from csvread ('C:\temp\temp\mqsiReportPropertiesToXmlAndSql\data\CML2A.BK\in.sql', 'brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, nodeValueVarchar, nodeValueClob', 'fieldDelimiter='' fieldSeparator=, escape=''');
insert into t (brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, nodeValueSha256, nodeValueVarchar, nodeValueClob) select brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, hash('SHA256', stringtoutf8(coalesce(nodeValueVarchar, nodeValueClob)), 2), nodeValueVarchar, nodeValueClob from csvread ('C:\temp\temp\mqsiReportPropertiesToXmlAndSql\data\CML3A.BK\in.sql', 'brokerName, executionGroupName, messageFlowName, nodeId, nodePid, intendation, xmlLineNumber, nodeXPath, nodeFullXPath, nodeName, nodeValueVarchar, nodeValueClob', 'fieldDelimiter='' fieldSeparator=, escape=''');

--create index if not exists it01 on t (brokerName);
--create index if not exists it02 on t (executionGroupName);
create index if not exists it03 on t (messageFlowName);
create index if not exists it04 on t (nodeFullXPath);
create index if not exists it05 on t (nodeName);
create index if not exists it06 on t (nodeValueSha256);
--create index if not exists it07 on t (brokerName, executionGroupName, messageFlowName);
--create index if not exists it08 on t (brokerName, executionGroupName, nodeFullXPath);
--create index if not exists it09 on t (nodeFullXPath, nodeValueSha256);
--create index if not exists it10 on t (nodeName, nodeValueSha256);

select count(*) from t;
select top 1000 * from t;

set LOG = 0; --no transaction log; OK for read only access
set CACHE_SIZE = 262144; --(a large cache is faster)
set LOCK_MODE = 0; --read uncommited; OK for read only access
set UNDO_LOG = 0; --no transaction log; OK for read only access

--shutdown defrag;

xml2MessageFlowPerFile.xsl

Code:

Last edited by t603 on Wed Aug 13, 2014 3:32 am; edited 2 times in total

t603

Posted: Wed May 14, 2014 12:07 am Post subject:

Voyager

Joined: 16 Oct 2012
Posts: 88
Location: Prague, the Czech Republic, Europe

Regarding the new version of the script from 2014-05-14 - in case of non well formed XML output it should be probably due missing IBM built in element or attribute in enumerations inside AWK script "script_a.awk". I do not have list of all possible elements and attributes in mqsireportproperties available, so my list is created by tests.

For new multiline attribute add it to the enumeration on the lines 78, 119, 125.

For new element add it to the enumeration on the line 90.

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » mqsireportproperties output in XML format?

Jump to:

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Protected by Anti-Spam ACP