First you need to have a XML document to validate. There are several ways to convert your data into XML documents. In previous article I showed you one such method. In this article I will show you how to do a validation with XML schema for existing XML document.
- well defined schema
- purely character based data definition method
- customizable tags
- easy validation of XML documents
To create Unique Key or Primary Key for a given table you need to go the table definition. Right click on the table that you want to modify and add new key and select "Definition" option.
Fig 1: Table Definition Option
Fig 1: Crystal Reports Tools in Microsoft Visual Studio 2005
Login to your iSeries Navigator and browse your database that you want to create new stored procedure and select "Procedures" section.
Fig 1: Select you database and Procedures in iSeries Navigator pane
Right click on Procedures and select "New" to create new stored procedure. There are two options to select either "SQL" or "External" type procedure to create. If you select ""SQL" you can write your new procedure using SQL syntax. Also you can use separate external program written using C, C++, CL, COBOL, COBOLLE, PLI, REXX, RPG, RPGLE and Java by selecting "External" option. This is very flexible method since high level programming languages are provided much more freedom to code complex coding than SQL language. This article is focus on develop a procedure using SQL language.
Fig 2: Create new SQL type procedure
Provide procedure name, description, maximum number of result set, data access and specific name in General tab of "New SQL Procedure" window.
Fig 3: New SQL Procedure General Tab
In Parameters tab you can specify what are parameters for the procedure. Click on "Insert" button to add new parameter, once you added new parameter you can change its name, data type, length and In/Out option.
Fig 4: New SQL Procedure Parameters Tab
Next step is develop SQL logic for the procedure. For that select "SQL Statements" tab in New SQL Procedure window. In SQL Statements tab you will have "SQL Statements examples" drop down list, Insert button and Statements section. From drop down list select what is the syntax that you want to insert into your code. This drow down list will show all the required SQL syntax to develop your SQL code. Once you select the statement, click on Insert button to enter selected statement into your code. After inserting default statement you can modify as you wish. For example you can change variable names, data types etc. Advantage here is you don't need familiar with all the syntax, but you can simply insert them and modify according to your requirement. You can put any number of statements to build up your SQL code.
Fig 5: New SQL Procedure SQL Statements Tab
In the above example I have inserted variable initialization and alter table SQL code. You can modify this inserted default code like below example.
SET myCounter= 4;
ALTER TABLE Customer ADD COLUMN Age INTEGER;
In first line I have initialized the variable called "myCounter" to 4. In second line I have alter the Customer table and add Age column.
In this way very easily you can develop your SQL program for your stored procedure. This is very efficient method and very time saving. But initially you might need to get little familiar with tool. Good luck and happy coding !
System.Net.Mail namespace should import using following code.
Code for prepare your E-mail format is as follows.
// System.Web.Mail.SmtpMail.SmtpServer is obsolete in 2.0
// System.Net.Mail.SmtpClient is the alternate class for this in 2.0
SmtpClient smtpClient = new SmtpClient();
MailMessage message = new MailMessage();
MailAddress fromAddress = new MailAddress(fromAdd, displayName);
// You can specify the host name or ipaddress of your server
// Default in IIS will be localhost
smtpClient.Host = "smtpsvr";
//Default port will be 25
smtpClient.Port = 25;
//From address will be given as a MailAddress Object
message.From = "firstname.lastname@example.org";
// To address collection of MailAddress
message.Subject = "Your E-mail subject";
// CC and BCC optional
// MailAddressCollection class is used to send the email to various users
// You can specify Address as new MailAddress("email@example.com")
// You can specify Address directly as string
//Body can be Html or text format
//Specify true if it is html message
message.IsBodyHtml = false;
// Message body content
message.Body = "Your E-mail message contents";
// Send SMTP mail
In this example you have set sessionState timeout value to 60 minutes. You can change this value as you wish, but need to make sure that it is really required. For example if you set a very large value for timeout, your session will hang on forever in the server. This will lead some security issues, memory overflow issues and sometime unexpected behaviour of your web site. And also this value should not be very small values. For example if you have functions like uploading files in the web site, then you should give enough time to upload a file based on the size of the file, network traffic and bandwidth limitation of the client machine.
You can find iSeries Navigator from "Start -> IBM iSeries Access for Windows -> iSeries Navigator". Open iSeries Navigator and select your database and table that you need insert identity column. Keep in mind that you can only set identity only for numeric fields. You can either create new table with identity columns or set identity property for existing table.
Fig 2: Set as Identity Column
Set Increment to an any value that you want increment your identity value. If you set 1 here it will increase the existing maximum number by 1. Starting value is used to set increase first row. Minimum value and Maximum value are used to restrict the values to a particular range. Values to cache is used to load last maximum values to memory so that increase the maximum value by Increment value is not needed a database hit for read maximum value for the identity column. "Cycle values when the maximum or minimum value is reached" check box is used to set whether the repetative value range is necessary when the datatype is reached its maximum or minimum values. This option is not allowed if you have set the column as primary or unique key for the table. "Generate the values in the order requested" check box is used to set whether you need to set values for the column in order or not. For example if you delete any value in between the value range whether that value is reusable or not.
Once you set any column as identity you don't need to give values when you insert a record into table. This column will automatically will be filled by the DBMS itself.
First step is to login to AS400 machine through iSeries Navigator. You need to provide server name, user id and password.
Once you successfully login to the system in "Environment" pane it will list down all the entities that you can manage through iSeries Navigator UI. This list will vary based on the access permission to the connected AS400 server. In this article will discuss on how to work with SQL tables through the Navigator. At the end of the article you will realize how you can save your time by using iSeries Navigator for database management rather working on console window.
Work with SQL tables in DB2 database through navigator you need to select "Databases" section. Under Databases section you will have Schemas, Database Navigator Maps, SQL Performance Monitors, SQL Plan Cache Snapshots and Transactions.
Select Schemas to Display
If you want to create a new database or schema, right click on Schemas and select "New -> Schema". Specify a name for the new schema, whether it needs to add to displayed list of schemas and create as standard library. "Create in" is the system pool that you want to create your database in. "Text" is to discribe shortly your database for operator's purpose.
Fig 5: New Schema Window
Create New Table
Once your database schema is listed under Schemas section, select the database that you want to work with. It will display all objects belong to the selected database such as aliases, constraints, distinct types, functions, indexes, journal receivers, journals, procedures, sequences, SQL packages, tables and views. Select "Tables" from the tree view and right hand side pane will display all the tables in the database. Right click on Tables and select "New -> Table" to create new table in the database.
Fig 6: Create New Table Window
In New Table window specify the table name, schema and system table name and text. System table name is used to identify the table in the system. Text is to describe the table purpose. Use "Volatile Data" check box if the table need to be volatile.
Add Column to Table
Fig 7: Create New Column Window
Specify Column name, Short name for system purposes, Data type, Text and Heading for new column. Data type drop down list shows all the data types that you can use for your purpose. Click "Add" once you enter all the details to add the column into table.
iSeries Navigator is stand alone windows application which will facilitate almost all the services that can perform in DB2. This is very rich tool but most of the people are still not used effectively.
Fig 2: iSeries Navigator UI
iSeries Navigator main UI containts four major sections and a menu bar. Menu bar is not a complex one and any person can easily work with. Top left hand corner section called "Environment" section and it list down all the entities and services that you can use based on your permission level. Top right hand corner section shows the details for the selected entity of the environment section. "My Tasks" section list down all the possible tasks that can perform at DB2 server level. This list is not unique for all users and will vary based on the permission level that user have. "Environment Tasks" section list down all the task that can perform for the selected environment in environment section.
Common functions listing that can perform using iSeries Navigator is as follows:
- Design and Create Databases
- Create Database Schemes
- Create Tables, Views, Stored Procedures, Functions, Indexes, Triggers and Journals
- Create Users, User Groups and manage them
- Manage Security
- Basic Operations such as Messages, Printer Output, Printers and Jobs
- Work Management - Manage Jobs, Queues, Subsystems and Memory Pools
- Network Configuration
- File System
- Backup Policies
- Application Developments
Will discuss one by one further in future articles.
Fig 5: Import Certificate Window