I have several queries which extract monthly data from a number of oracle and sql server databases. I have created tables in local storage which currently hold 12 months worth of data. I am trying to automate appending ‘last months’ data to these tables but I’m not sure how? I’ve had a good search and I can see how to refresh snapshots. Is it possible to have a query write new data to a local table and then automate it.
I have made some progress. I am using the import wizard to write the data to the Local Storage table. Next question is I am using some sql to list another table’s column (called ‘Element Name’) and using a loop dataset to try and use this as a variable to feed to my import wizard query. I know I should use a colon in the code to represent my variable but if I just use the variable name I get a ‘not all variables bound’ message - not sure what the syntax should be here?
Can you publish a screenshot with this message and the script (.tas) itself?
Thanks,
Igor.
Here is the code I’m trying to run. I’ve simply replaced the text parameter at the beginning of the Where clause with :VARBMC.
I have setup VARBMC by using an Execute Script command and set the Save Result Set in Variable to VARBMC.
I can run the Exectute Script command and is does return a single column of text values.
I am running the code on a virtual machine and I don’t have a way to get a screen shot of the error but basically it is an Oracle database error - ORA-01008: not all variables bound
SELECT VW_NAMESPACE.ELEMENT_NAME,
VW_NAMESPACE.ELEMENTSTATUS,
VW_NAMESPACE.APPLICATIONNAME,
VW_NAMESPACE.INSTANCENAME,
VW_NAMESPACE.PARAMETER_NAME,
VW_NAMESPACE.PARAMETER_UNIT,
PARAM_SUMMARIZATION_DATA_POINT.VALUESUM
/ PARAM_SUMMARIZATION_DATA_POINT.VALUECOUNT
AS VALUEAVG,
PARAM_SUMMARIZATION_DATA_POINT.VALUECOUNT,
TO_DATE (
TO_DATE (‘01-JAN-1970’, ‘dd-mon-yyyy’)
- PARAM_SUMMARIZATION_DATA_POINT.TIMESTAMP / 24 / 60 / 60 / 1000,
‘dd/mm/yy’)
AS TIMECODE,
VW_NAMESPACE.WARNINGENABLE,
VW_NAMESPACE.WARNINGMINVALUE
FROM PE.VW_NAMESPACE VW_NAMESPACE
INNER JOIN
PE.PARAM_SUMMARIZATION_DATA_POINT PARAM_SUMMARIZATION_DATA_POINT
ON (VW_NAMESPACE.PARAMETER_INSTANCE_GUID =
PARAM_SUMMARIZATION_DATA_POINT.“PARAMETER”)
WHERE VW_NAMESPACE.ELEMENT_NAME = :VARBMC
AND VW_NAMESPACE.PARAMETER_NAME = ‘CPU Usage’
AND TO_DATE (
TO_DATE (‘01-JAN-1970’, ‘dd-mon-yyyy’)
- PARAM_SUMMARIZATION_DATA_POINT.TIMESTAMP
/ 24
/ 60
/ 60
/ 1000,
‘dd/mm/yy’) BETWEEN TRUNC (TRUNC (SYSDATE, ‘MM’) - 1, ‘MM’)
AND TRUNC (SYSDATE, ‘MM’) - 1
If I understand correctly, you have defined your VARBMC variable in Execute Script activity. In this case VARBMC represents the whole result set. Referencing it as :VARBMC returns the first column of the first row. Are you sure you need exactly this? To access a particular column of this dataset you should use “dot notation” like this:
:VARBMC.ColName
where ColName is the name of the column of the dataset returned by the query from you Execute Script activity.
Accessing a particular data row is only available in Loop Dataset activity when you use VARBMC in it.
Again, can you publish your Automation script? Having it would be easier to understand what you’re trying to accomplish.
Thanks,
Igor.
Not sure how I should ‘publish’ it but the XML script is below: The query I’m using only has a single row so by your description above should work?
<?xml version="1.0" encoding="utf-16"?><![CDATA[<ta0:DefaultDescriptionActivity ProdConnection="{x:Null}" UseProjectManager=“True” LogDir=“C:\Users\admnriley3\AppData\Roaming\Quest Software\Toad Data Point 3.3\Automation” Notes=“Script Attributes” Description=“Script Attributes” TestConnection="{x:Null}" Body="" EmbedSql=“False” Enabled=“True” RelativePath=“False” SmtpPort=“25” ScriptVersion=“2.34” x:Name=“BMC_WriteDatatoLocalStorage” Mode=“Test” ScriptName=“BMC_WriteDatatoLocalStorage” ContinueOnError=“stopOnError” Authentication=“Version=1,AuthenticationMethod=None” Subject=“Automation Script Error”>
ta0:DefaultDescriptionActivity.AttachmentFiles
<x:Array Type="{x:Type ta0:FileDescription}">
<ta0:FileDescription FileName=“C:\Users\admnriley3\AppData\Roaming\Quest Software\Toad Data Point 3.3\Automation\BMC_WriteDatatoLocalStorage.log” Description="" />
</x:Array>
</ta0:DefaultDescriptionActivity.AttachmentFiles>
</ta0:DefaultDescriptionActivity>
<ta0:VariableLoopDatasetActivity RowCountVar=“Loop_data_1_RCOUNT” Sql=“SELECT BMC_Master_Elements.Element Name
FROM TDP.BMC_Master_Elements BMC_Master_Elements” DataTable=“Loop_data_1_SQL” x:Name=“Loop_data_1” Description=“Loop through result set and execute included activities” DatasetIndex=“Loop_data_1_SQL”>
ta0:VariableLoopDatasetActivity.ConnectDesc
<ta1:ConnectionDescription Description=“A2MON808;BMCPDS (PE), PE” NodePath=“Oracle\BMC Portal Data” Trl=“oracle://PE@A2MON808/” IsNullPassword=“False” />
</ta0:VariableLoopDatasetActivity.ConnectDesc>
<ta0:VariableLoopDatasetBranchActivity x:Name=“Loop_row_1”>
<ta1:SelectDataActivity IsSnapshot=“True” DatabaseName="" LinkFile=“False” SaveDestinationType=“LocalStorage” DatasetName="" OnError=“stopOnError” x:Name=“Execute_1” SqlScript=“SELECT BMC_Master_Elements.Element Name
FROM TDP.BMC_Master_Elements BMC_Master_Elements” RowCountVar=“Execute_1_RCOUNT” TruncateTable=“True” TableName="" TruncateDataset=“True” SqlScriptEmbed="" Description=“Execute a SQL script and save result in a variable or export result to Local Storage or Toad Intelligence Central” ConnectionDescriptionTS="{x:Null}" ResultVariable=“VARBMC”>
ta1:SelectDataActivity.ConnectionDescription
<ta1:ConnectionDescription Description=“root@localhost” NodePath=“Local Storage\Local Storage Connection” Trl=“localdb://root@localhost/” IsNullPassword=“True” />
</ta1:SelectDataActivity.ConnectionDescription>
</ta1:SelectDataActivity>
<ta1:ImportWizardActivity ImportTemplate=“D:\CapPlan Data\BMC Portal\BMC_to_Local.tim” UseAutomationConnection=“True” OnError=“stopOnError” Description=“Import data from a file or database using the Import Wizard” ImportTemplateXml="" x:Name=“Import_1”>
ta1:ImportWizardActivity.ConnectionDescription
<ta1:ConnectionDescription Description=“root@localhost” NodePath=“Local Storage\Local Storage Connection” Trl=“localdb://root@localhost/” IsNullPassword=“False” />
</ta1:ImportWizardActivity.ConnectionDescription>
</ta1:ImportWizardActivity>
</ta0:VariableLoopDatasetBranchActivity>
</ta0:VariableLoopDatasetActivity>
</ta:ToadAutomationScript>]]>
As my replies don’t seem to be eliciting much response perhaps I should explain exactly what I am trying to do.
I have a number of systems which are collecting performance metrics from other servers.
What I want to do is have a list in local storage which has the server names in.
I then want to loop through this list running a monthly extract query and appending the data to tables in local storage which I built last month.
Sounds simple - but I’m stuck as to how to make this work. Having looked at the automation tutorials I’m pretty sure this should be achievable but I don’t seem to be able to get the variables to work
Any ideas anyone?
See this video for how to use a loopdata set.
To append data to Local Storage use the Execute Script activity with the save to local storage option. Make sure and uncheck the overwrite option.
Thanks for that Debbie - I was trying to use import. I’ve tried running a loop dataset and it reads my table correctly but it then gives the following error
Currently, export to Locale Storage is not supported if query uses parameters.
I am trying to use the variable in the loop dataset to feed the query to then append to the local table - is this not possible as stated?
Is this likely to be included in a later release - i’m having to work very hard to work my way around this?
Is this thread resolved? We do support bind vars in SQL. We had an issue the other day where the name of the table being exported to could not support a bind var. But we fixed that today and will be in next Beta. But from what I see from thread everything should be fine. If you still have an issue please email we directly and we can resolve. Debbie.Peabody@quest.com
Yes - resolved now. It was me who raised the other issue you mentioned too. It appears that I had misunderstood whether I needed to use a Bind Variable or just a straightforward variable enclosed in hashes (pounds). Many thanks for your help on this and I look forward to the next beta - will make my life much easier 