Hi, I'm a complete newbie as far as PIP is concerned, but I'm an experienced M developer (20 years of DSM, MSM, OpenM and some GT.M) and have done a lot of M to SQL/object mapping in my time. This was originally in M/SQL, but I've also worked extensively with Cache objects and SQL.
I have just installed PIP and would like to start creating my own structures, and mapping to existing M data.
I understand from Bhaskar that I can't create classes yet, but that I should be able to create tables which can be amended to read current M structures, though the documentation isn't there yet.
Any help anyone could give me would be greatly appreciated.
Thanks,
Mike
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
This is preliminary information, i.e., not heavily edited, some gaps, and at this point a cut-and-paste from a Word document, but it should answer some of your questions:
Basic information on setting up existing globals to be able to be accessed by SQL and PSL
Menu and function access
PIP has a menu system that is accessed by DO ^DRV at the GTM prompt.
User identification: 1
Enter your password: xxx (lower case)
While considerable configuration can be done using SQL or other GUI PIP tools, some basic and/or initial operations may be simpler through use of the menu system.
The menu system can be navigated by entry of the number displayed or the first character(s) of the item name. To move back up the menu, use the up-arrow. Each item either links to another menu or to a function. (Enter ?? at the prompt to see either the linked menu number or function name).
Functions can be accessed directly by pre-pending an @ in front of the function name, e.g., @DBSDFC.
The menu system is stored in ^SCATBL(0,menunumber,seq)=data (table SCAMENU)
Functions are stored in ^SCATBL(1,function)=data (table SCATBL)
Function authorization (by userclass) is stored in ^SCATBL(1,function,userclass)=1 (table SCATBL3)
Special keys useful on screens: (Note, we need to figure out the mapping for putty. These are the keys that map in SmarTerm)
F11 – escape
Pause – DO, i.e., move to end of screen to take file/next screen action
End or ?– select (where applicable, displays a select list)
Home – find (where applicable, allows search against a select list)
Print Screen – help (displays field help, if available)
Screen notes:
• On most screens, highlighted fields indicate required
• Pressing the help key on a field will display a sub-menu. Field-help displays the table and column name ([table]column is the syntax, equivalent to table.column), and select data dictionary attributes for the column, as well as any documentation available for the field. Arrow keys, as well as page down and page up can be used to navigate the help pane, and F11 will escape.
• Pressing the DO key will move to either the next required field or to the end of the screen, where a sub-menu of next options are presented. The sub-menu can be navigated with the arrow keys or the first character of each item. Press return to select an item.
Mapping of Global to PIP
Globals are mapped via tables DBTBL1 and DBTBL1D. These tables are represented by global ^DBTBL(“SYSDEV”,1,tablename.
Table DBTBL1 contains the table definition information, while table DBTBL1D contains the column definitions (^DBTBL(“SYSDEV”,1,tablename,9,columnname)).
Two functions are particularly useful for creation and maintenance of PIP table definitions - DBSDFC (Create file definition) and DBSDFM (Modify file definition).
[Historical notes:
• The literal key “SYSDEV” in the global DBTBL is there for historical reasons, and should be treated as a constant at this point
• Terminology such as “file definition” pre-dates SQL table support, but read “file” in this context as “table”
• In various contexts, table.column will be displayed at [table]column
• Similarly, you will see references to FID, which stands for file ID, or, now, table name; and DI, for data item, or column
]
Creating a table definition (function DBSDFC)
Create a table definition to either map an existing, unmapped global to PIP, or to create a new definition for a new table.
The basic concept is that a table definition maps a global that has data stored on a node, associated with the keys, and/or next-level sub-nodes under they keys. On each node, multiple pieces of data (columns), can be stored, separated by a delimiter defined as part of the table definition. For example, these two globals could each be mapped to a separate table:
Depending on the structure of existing globals, it may be necessary to use more than a single table definition to map a single global.
Access function DBSDFC - at the file name prompt, enter the name you wish to give the table (names must be uppercase).
You will be presented with a table definition screen. The following are the most important fields. Note that most have help available. The table and column of the field is indicate in parenthesis.
• System name (DBTBL1.SYSSN) – select PBS initially, until you have had further time to configure PIP to your needs.
• Documentation file name (DBTBL1.FDOC) – in general, use the same name as the table name
• Global name (DBTBL1.GLOBAL) – enter the name of the global where the date is or will be stored, without an up-arrow or sub-scripts. Even if the data is going to be stored in an Oracle database, a global name, although it won’t be used, is required.
• Primary Key(s) (DBTBL1.ACCKEYS)– enter the global keys, as literals or by column name, comma separated. For example, the table DBTBL1D, which maps columns, has keys - %LIBS,1,FID,9,DI ; but, since the first key is now a literal, could be set up with keys – “SYSDEV”,1,FID,9,DI.
• Record Type (DBTBL1.RECTYP) – valid values are equivalent to the $Data result (note that while 0 is listed as a valid option, it is not useful in this context):
o 1 – for a global where all the data will be stored on the node represented by the Primary Keys, e.g., ^ABC(key1,key2) = “col1|col2|col3”
o 10 – for a global where all the data will be stored on nodes under the Primary Keys. E.g., if the primary key is ACCTNO (account number),
^ABC(ACCTNO,1) = “col1|col2|…”
^ABC(ACCTNO,25) = “col10|col11|…”
^ABC(ACCTNO,”address”) = “street|city|state|…”
The values of the storage nodes are specified when defining columns.
o 11 – for a global that combines both 1 and 10, storing some data at the key level and other at nodes under the keys.
• Delimiter (DBTBL1.DEL) – the ASCII value of the character that is used to separate data (columns) stored on the same name. The up-bar (ASCII 124) is used in the examples in this document.
Once the table definition is filed, you will be presented with screens to define columns. This can also be accessed with the file maintenance function, DBSDFM, discussed below.
Create a column definition to map data in a global to a column of the table, recognized by SQL and PSL.
Access function DBSDFM - at the file name prompt, enter the name of the table to which the columns apply. Select the “Modify Data Items” option to create/modify columns.
You will be presented with a column definition screen. The following are the most important fields. Note that most have help available. The table and column of the field is indicate in parenthesis.
• Data Item (DBTBL1D.DI) – the name of the column (must be uppercase). Entry of a new column will allow it to be defined. Entry of an existing column will display it’s current definition for maintenance. Press SELECT to get a list of all currently existing columns for the table.
• Data Type (DBTBL1D.TYP) – press SELECT to see the supported data types
• Length (DBTBL1D.LEN) – the maximum length of column values
• Pre/Post-Processor fields – these fields support old screen-based functionality, and will be removed in the future. Do no use them.
• Null (DBTBL1D.NULLIND) – this field is no longer supported, and should be left as the default value of N
After the first page, a second page will be presented. This page provides the mapping information:
• Sub Record ID (DBTBL1D.NOD) – for tables of record type 1, this will be the bottom key name; for tables of record type 10, enter a node, either a number or literal (quoted string), which will be the sub-node on which this column is stored; for tables of record type 11, either the bottom key name or a sub-node can be entered.
• Column (DBTBL1D.POS) – the location of the column in the data stored on the indicate node (sub-record ID), based on the table delimiter. For example, in the table ACCOUNT, stored in global ACCT, with primary key accNo, and with record type 10, table delimiter of 124 (up-bar):
^ACCT(acctNo,5) = “firstName|lastName|balance|…”
For column balance, the sub-record ID is 5 (as it is for firstName and lastName), and the Column (DBTBL1D.POS) is 3, i.e., the 3rd piece of the string, delimited by “|”.
• Computed Expression (DBTBL1D.CMP) – PIP support computed columns. These are columns that do not exist in the database, but are computation based on other columns in the table. For example, AGE would be computed based on data of birth. In order to enter the formula for a computed column, leave the sub-record ID empty.
While there are considerably more features to PIP table definitions, and numerous fields on the screens mentioned above not touched on, this should be sufficient to get globals mapped in order to get started.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi, I'm a complete newbie as far as PIP is concerned, but I'm an experienced M developer (20 years of DSM, MSM, OpenM and some GT.M) and have done a lot of M to SQL/object mapping in my time. This was originally in M/SQL, but I've also worked extensively with Cache objects and SQL.
I have just installed PIP and would like to start creating my own structures, and mapping to existing M data.
I understand from Bhaskar that I can't create classes yet, but that I should be able to create tables which can be amended to read current M structures, though the documentation isn't there yet.
Any help anyone could give me would be greatly appreciated.
Thanks,
Mike
Mike,
This is preliminary information, i.e., not heavily edited, some gaps, and at this point a cut-and-paste from a Word document, but it should answer some of your questions:
Basic information on setting up existing globals to be able to be accessed by SQL and PSL
Menu and function access
PIP has a menu system that is accessed by DO ^DRV at the GTM prompt.
User identification: 1
Enter your password: xxx (lower case)
While considerable configuration can be done using SQL or other GUI PIP tools, some basic and/or initial operations may be simpler through use of the menu system.
The menu system can be navigated by entry of the number displayed or the first character(s) of the item name. To move back up the menu, use the up-arrow. Each item either links to another menu or to a function. (Enter ?? at the prompt to see either the linked menu number or function name).
Functions can be accessed directly by pre-pending an @ in front of the function name, e.g., @DBSDFC.
The menu system is stored in ^SCATBL(0,menunumber,seq)=data (table SCAMENU)
Functions are stored in ^SCATBL(1,function)=data (table SCATBL)
Function authorization (by userclass) is stored in ^SCATBL(1,function,userclass)=1 (table SCATBL3)
Special keys useful on screens: (Note, we need to figure out the mapping for putty. These are the keys that map in SmarTerm)
F11 – escape
Pause – DO, i.e., move to end of screen to take file/next screen action
End or ?– select (where applicable, displays a select list)
Home – find (where applicable, allows search against a select list)
Print Screen – help (displays field help, if available)
Screen notes:
• On most screens, highlighted fields indicate required
• Pressing the help key on a field will display a sub-menu. Field-help displays the table and column name ([table]column is the syntax, equivalent to table.column), and select data dictionary attributes for the column, as well as any documentation available for the field. Arrow keys, as well as page down and page up can be used to navigate the help pane, and F11 will escape.
• Pressing the DO key will move to either the next required field or to the end of the screen, where a sub-menu of next options are presented. The sub-menu can be navigated with the arrow keys or the first character of each item. Press return to select an item.
Mapping of Global to PIP
Globals are mapped via tables DBTBL1 and DBTBL1D. These tables are represented by global ^DBTBL(“SYSDEV”,1,tablename.
Table DBTBL1 contains the table definition information, while table DBTBL1D contains the column definitions (^DBTBL(“SYSDEV”,1,tablename,9,columnname)).
Two functions are particularly useful for creation and maintenance of PIP table definitions - DBSDFC (Create file definition) and DBSDFM (Modify file definition).
[Historical notes:
• The literal key “SYSDEV” in the global DBTBL is there for historical reasons, and should be treated as a constant at this point
• Terminology such as “file definition” pre-dates SQL table support, but read “file” in this context as “table”
• In various contexts, table.column will be displayed at [table]column
• Similarly, you will see references to FID, which stands for file ID, or, now, table name; and DI, for data item, or column
]
Creating a table definition (function DBSDFC)
Create a table definition to either map an existing, unmapped global to PIP, or to create a new definition for a new table.
The basic concept is that a table definition maps a global that has data stored on a node, associated with the keys, and/or next-level sub-nodes under they keys. On each node, multiple pieces of data (columns), can be stored, separated by a delimiter defined as part of the table definition. For example, these two globals could each be mapped to a separate table:
• ^CUSTOMER(custNo)=”name|dob|city|state|…”
• ^ACCOUNT(acctNo,1)=”custNo|balance|openDate|…”
^ACCOUNT(acctNo,5)=”accruedAmt|overdraftAmt|…”
Depending on the structure of existing globals, it may be necessary to use more than a single table definition to map a single global.
Access function DBSDFC - at the file name prompt, enter the name you wish to give the table (names must be uppercase).
You will be presented with a table definition screen. The following are the most important fields. Note that most have help available. The table and column of the field is indicate in parenthesis.
• System name (DBTBL1.SYSSN) – select PBS initially, until you have had further time to configure PIP to your needs.
• Documentation file name (DBTBL1.FDOC) – in general, use the same name as the table name
• Global name (DBTBL1.GLOBAL) – enter the name of the global where the date is or will be stored, without an up-arrow or sub-scripts. Even if the data is going to be stored in an Oracle database, a global name, although it won’t be used, is required.
• Primary Key(s) (DBTBL1.ACCKEYS)– enter the global keys, as literals or by column name, comma separated. For example, the table DBTBL1D, which maps columns, has keys - %LIBS,1,FID,9,DI ; but, since the first key is now a literal, could be set up with keys – “SYSDEV”,1,FID,9,DI.
• Record Type (DBTBL1.RECTYP) – valid values are equivalent to the $Data result (note that while 0 is listed as a valid option, it is not useful in this context):
o 1 – for a global where all the data will be stored on the node represented by the Primary Keys, e.g., ^ABC(key1,key2) = “col1|col2|col3”
o 10 – for a global where all the data will be stored on nodes under the Primary Keys. E.g., if the primary key is ACCTNO (account number),
^ABC(ACCTNO,1) = “col1|col2|…”
^ABC(ACCTNO,25) = “col10|col11|…”
^ABC(ACCTNO,”address”) = “street|city|state|…”
The values of the storage nodes are specified when defining columns.
o 11 – for a global that combines both 1 and 10, storing some data at the key level and other at nodes under the keys.
• Delimiter (DBTBL1.DEL) – the ASCII value of the character that is used to separate data (columns) stored on the same name. The up-bar (ASCII 124) is used in the examples in this document.
Once the table definition is filed, you will be presented with screens to define columns. This can also be accessed with the file maintenance function, DBSDFM, discussed below.
Creating/Modifying Column Definitions (function DBSDFM)
Create a column definition to map data in a global to a column of the table, recognized by SQL and PSL.
Access function DBSDFM - at the file name prompt, enter the name of the table to which the columns apply. Select the “Modify Data Items” option to create/modify columns.
You will be presented with a column definition screen. The following are the most important fields. Note that most have help available. The table and column of the field is indicate in parenthesis.
• Data Item (DBTBL1D.DI) – the name of the column (must be uppercase). Entry of a new column will allow it to be defined. Entry of an existing column will display it’s current definition for maintenance. Press SELECT to get a list of all currently existing columns for the table.
• Data Type (DBTBL1D.TYP) – press SELECT to see the supported data types
• Length (DBTBL1D.LEN) – the maximum length of column values
• Pre/Post-Processor fields – these fields support old screen-based functionality, and will be removed in the future. Do no use them.
• Null (DBTBL1D.NULLIND) – this field is no longer supported, and should be left as the default value of N
After the first page, a second page will be presented. This page provides the mapping information:
• Sub Record ID (DBTBL1D.NOD) – for tables of record type 1, this will be the bottom key name; for tables of record type 10, enter a node, either a number or literal (quoted string), which will be the sub-node on which this column is stored; for tables of record type 11, either the bottom key name or a sub-node can be entered.
• Column (DBTBL1D.POS) – the location of the column in the data stored on the indicate node (sub-record ID), based on the table delimiter. For example, in the table ACCOUNT, stored in global ACCT, with primary key accNo, and with record type 10, table delimiter of 124 (up-bar):
^ACCT(acctNo,5) = “firstName|lastName|balance|…”
For column balance, the sub-record ID is 5 (as it is for firstName and lastName), and the Column (DBTBL1D.POS) is 3, i.e., the 3rd piece of the string, delimited by “|”.
• Computed Expression (DBTBL1D.CMP) – PIP support computed columns. These are columns that do not exist in the database, but are computation based on other columns in the table. For example, AGE would be computed based on data of birth. In order to enter the formula for a computed column, leave the sub-record ID empty.
While there are considerably more features to PIP table definitions, and numerous fields on the screens mentioned above not touched on, this should be sufficient to get globals mapped in order to get started.