How to start DB learnings

Hi All,

I have a strong desire to learn Agile DB , any documents or articles available for that ,I am just starting as a fresher ,any pre requisite learnings needed for that ?
I could not find any documents in Oracle unlike we have for SDK, administration.


No Files Were Attached
Surya Agile Talent Asked on May 11, 2018 in Others.
Add Comment
7 Answer(s)

Are you referring to learning the Agile database schema (like how BOMs work or the History tab works)?  This will require DB connection and basic SQL.  
Or are you referring to learning database maintenance, installation, backup, etc.?   For this, you can find Agile 9.3 documentation on Database Installation and Upgrade, among other information on the Oracle Knowledge Zone website.

Matt Paulhus Agile Angel Answered on May 11, 2018.
Add Comment

I’m referring to Agile DB schema ,I know little about SQL .

Surya Agile Talent Answered on May 12, 2018.
Add Comment

There is none, but here’s a couple of things to help:
1) You’ll use basic Oracle SQL.  Just teach yourself basic SQL and there’s a few small difference being an Oracle server that you’ll need to pick up on.
2) The “Operational Data Reference Manual” from the OPLA Admin manual found here isn’t everything, but did help me with possible tables and rows.
3) I couldn’t find the Youtube video where I got this from so I’ll just post my template.  Here’s the basic Groovy script that you can run within the Event Handler within Agile.
4) There’s a SQL line where you can get a list of all DB tables w/ DB possible rows.  Try googling it.  

import com.agile.agileDSL.ScriptObj.*;
import com.agile.api.*;
import com.agile.px.*;
import com.agile.util.sql.*;
import java.sql.*;

void invokeScript(IBaseScriptObj obj) {

Connection connection = null;
PreparedStatement statement = null;
ResultSet rows = null;
IAgileSession session = obj.getAgileSDKSession();

try {
connection = ConnectionFactory.getFactory().getConnection();
String sqlquery = “select LOGINID, TITLE from AGILEUSER”    //here’s where you write the actual SQL script
statement = connection.prepareStatement(sqlquery);

rows = statement.executeQuery();

obj.logMonitor(“List of All Agile Users\nLogin ID~Title”)
while( {

obj.logMonitor(rows.getString(“LOGINID”)+”~”+rows.getString(“TITLE”))     }

} catch (Exception e) {
obj.logMonitor( e.getMessage() );
finally {

Matt Paulhus Agile Angel Answered on May 14, 2018.
Add Comment

Thanks a lot Matt for your detailed structure of learning procedure ,I will start with this today.


Surya Agile Talent Answered on May 14, 2018.
Add Comment

Hi Surya,

As Matt mentioned, you need to get accustomed to basic Oracle SQL to start with. Also wouldn’t recommend any Insert/Delete  statements, start with Select and then gradually to Update. 

Secondly if you have toad installed then connect to Agile-schema and load your schema browser. In Table column you can see some important tables such as Item, BOM, Manu_Parts,Page_Two,Page_Three etc.  You can see the view tab for basic pre-loaded views such as item_p2 or item_p2p3.

Also the other thing you need to understand how you fetch values for different data-types as it’s different for list/multi-list and fetching them from flex table is also bit complex. 

One thing i can recommend from my personal experience is start with simple queries to display an Item and It’s P2,P3 values and then proceed with others. 


Arif Agile Angel Answered on May 14, 2018.
Add Comment

I’ll also add that everything in Agile is serialized by an ID number – therefore, you’ll have to teach yourself how to join tables so that way you can get something like the CHANGE_NUMBER field from the CHANGES table even though a different table you were wanting to work with, like the SIGNOFF table doesn’t have your ECO number itself but rather its serialized ID.

You can cross reference and find the objects serialized ID number via the navigator like so.  Notice in the bottom right circled blocks – the number 6000 is for Change Orders while the 7130770 is the serial number ID for the CN46530 that I’m highlighting.  

//this query gets you the column names and data type for every row field of a particular table.  Taking out the where statement should get you every table but you’ll need to add the table name column.  
“select column_name, data_type from user_tab_cols WHERE table_name = ‘ITEM_P2P3_QUERY'”);

Matt Paulhus Agile Angel Answered on May 14, 2018.
Add Comment

Thanks Arif and Matt for your help.
Three years back when I was starting SDK Arif helped me to get started,now I am a Agile developer,this will surely help many beginners to set ball rolling.

Surya Agile Talent Answered on May 14, 2018.

Glad it worked out well for you mate. All the best, let us know if you need input.

on May 14, 2018.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.