====== System i Support ======

Mantis support for IBM System i platform (AS/400, iSeries) and DB2.

===== [[MySQLi5|Installing MySQL on System i]] =====


http://dev.mysql.com/downloads/mysql/5.0.html#downloads\\ 
(as of 2007-01-30)\\ 

  IBM AIX downloads 
  AIX 5.2 (POWER, 64-bit)	Standard	5.0.27	28.1M	
                          Max	        5.0.27	28.1M	

Download filename:  mysql-max-5.0.27-aix5.2-powerpc-64bit.tar.gz\\ 

  W:\LIVE\mantis-sql>dir
   Directory of W:\LIVE\mantis-sql
  10/20/2006  21:24           80,732,160 mysql-max-5.0.27-aix5.2-powerpc-64bit.tar
  
  W:\LIVE\mantis-sql>ftp   -i   as400
  Connected to AS400.
  220-QTCP at 192.168.0.222.
  220 Connection will close if idle more than 5 minutes.
  User (RUBY:(none)): yourUserId
  331 Enter password.
  Password: **********
  230 YOURUSERID logged on.
  ftp> cd /usr/local
  550-NAMEFMT set to 1.
  250 "/usr/local" is current directory.
  ftp> ls
  200 PORT subcommand request successful.
  125 List started.
  Zend/
  250 List completed.
  ftp: 7 bytes received in 0.00Seconds 7000.00Kbytes/sec.
  ftp> bin
  200 Representation type is binary IMAGE.
  ftp> put *.tar
  200 PORT subcommand request successful.
  150 Sending file to /usr/local/mysql-max-5.0.27-aix5.2-powerpc-64bit.tar
  ftp: 80732160 bytes sent in 11.36Seconds 7107.33Kbytes/sec.
  ftp>quit

On the AS/400 command line, as QSECOFR, issue the following commands (responses not shown):\\
  call  qp2term
  cd  /usr/local
  tar  -xvf  mysql-max-5.0.27-aix5.2-powerpc-64bit.tar 
  ln -s mysql-max-5.0.27-aix5.2-powerpc-64bit  mysql 
  cd /usr/local/mysql
  scripts/mysql_install_db

Start MySQL
  > cd . ; ./bin/mysqld_safe &                                        
    [1]     128192                                                    
    # Starting mysqld daemon with databases from /usr/local/mysql/data

Continue with Unix Post Install docs at 
http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html    

First, set default priviledges:
http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html

  > ./bin/mysql -u  root
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1 to server version: 5.0.27-max
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    mysql>
  > SET PASSWORD FOR ''@'localhost' = PASSWORD('secret');
    Query OK, 0 rows affected (0.00 sec)
    mysql>
  > SELECT Host, User FROM mysql.user;
    +-------------------+------+
    | Host              | User |
    +-------------------+------+
    | AS400             |      |
    | AS400             | root |
    | localhost         |      |
    | localhost         | root |
    +-------------------+------+
    4 rows in set (0.01 sec)
    mysql>
  > SET PASSWORD FOR ''@'AS400' = PASSWORD('secret');
    Query OK, 0 rows affected (0.06 sec)
    mysql>
  > UPDATE mysql.user SET Password = PASSWORD('secret') -> WHERE User = '';
    Query OK, 0 rows affected (0.07 sec)     
    Rows matched: 2  Changed: 0  Warnings: 0 
    mysql>
  > UPDATE mysql.user SET Password = PASSWORD('secret')  WHERE User = 'root';
    Query OK, 2 rows affected (0.01 sec)
    Rows matched: 2  Changed: 2  Warnings: 0
    mysql>
  > FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    mysql>

Optionally remove anonymous access:
  mysql> DELETE FROM mysql.user WHERE User = ''; 
  mysql> FLUSH PRIVILEGES;

Validate with:
  > ./bin/mysqladmin version  -psecret
    ./bin/mysqladmin  Ver 8.41 Distrib 5.0.27, for ibm-aix5.2.0.0 on powerpc
    Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
    This software comes with ABSOLUTELY NO WARRANTY. This is free software,
    and you are welcome to modify and redistribute it under the GPL license
    Server version          5.0.27-max
    Protocol version        10
    Connection              Localhost via UNIX socket
    UNIX socket             /tmp/mysql.sock
    Uptime:                 37 min 56 sec
    Threads: 1  Questions: 15  Slow queries: 0  Opens: 12  Flush tables: 2  Open tables: 0  Queries per second avg: 0.007
    #

Now edit ZendCore to tell it to use MySQL.  We will do this manually by editing the php.ini file.  Go to an AS/400 command line and issue the command:
  wrklnk  '/usr/local/Zend/core/etc' 

Place a 5 in the option column at ETC and Press Enter.

Place a 2 in the option column at PHP.INI and Press Enter.

Use the search command:
  f  =mysql
to find the lines:
  ;extension=mysql.so
  ;extension=mysqli.so

It is possible that they are already un-commented (without a leading semicolon).  If not, remove the leading semicolon resulting in:
  extension=mysql.so
  extension=mysqli.so

Save with F2 and exit with F3.

At this time, you should end and re-start PHP from the green screen menu at ZENDCORE/ZCMENU.  When it starts back up, everything should be operational.




----
----
----
----
===== [[Mantisi5|Installing MANTIS]] =====


Obtain the latest stable version of Mantis from http://sourceforge.net/project/showfiles.php?group_id=14963&package_id=166159
Download the file mantis-1.0.6.tar.gz to a PC folder.  Since the file is "GZIPPED", the easiest thing to do is use WinZip on a Windoze box to unzip (untar) the distribution file.

Unzip gz file to a known folder on your PC.  Use a real FTP program like WS-FTP or CuteFTP (free) to transfer the folder and all subfolders and files to a folder on the AS/400.  We suggest you place the contents in the default http documents folder in the root file system of the IFS:
  /www/zendcore/htdocs/xxx
where xxx is either the default folder name ''mantis-1.0.6'' (ugly to key in) or a more convenient name like ''mantis'' or ''bugtracker'' or ''issues''.  For this example we will use the folder ''mm'' standing for Mantis on MySQL.

Start the install process by using your browser to run the index.php in the root folder of the Mantis software:
  http://your_system_ip_or_name:89/mm/index.php

Mantis will automatically detect that it is not yet installed, as this is the same file that is used to run the software also.  Provide the userIDs and passwords you configured for MySQL in the previous steps.




Once that is complete, you should modify the default configuration by editing the config_inc.php file in the main folder of your Mantis installation.  The Mantis system uses two configuration files.  The default file config_defaults_inc.php is  not to be modified.  All modifications to the configuration go into config_inc.php, which is read AFTER config_defaults_inc.php, and therefore overrides it.  Following are some setting that should be set in config_inc.php, most of them will need to be added:


  <?php
        # leave these next three the same
	$g_hostname = 'localhost';
	$g_db_type = 'mysql';
	$g_database_name = 'bugtracker';
        # your database userid goes here
	$g_db_username = 'root';
        # your password goes here
	$g_db_password = 'secretpassword';
	# Used to link to manual for User Documentation.
	# Updated URL for the Mantis online docs
	$g_manual_url = 'http://www.mantisbt.org/manual/index.php/';
	# String used to generate the confirm_hash for the 'lost password' feature and captcha code for 'signup'
	# ATTENTION: CHANGE IT TO WHATEVER VALUE YOU PREFER
	$g_password_confirm_hash_magic_string = 'cornedbeefhash';
	# these next two require you to upload the file arial.ttf from your PC to a folder you create
	# absolute path (with trailing slash!) to folder which contains your TrueType-Font files
	# used to create the captcha image and since 0.19.3 for the Relationship Graphs
	$g_system_font_folder	= './fonts/';
	# font name used to create the captcha image. i.e. arial.ttf
	# (the font file has to exist in the system_font_folder)
	$g_font_per_captcha	= 'arial.ttf';
	# --- email variables -------------
	$g_administrator_email	= 'ira@curbstone.com';
	$g_webmaster_email		= 'webmaster@curbstone.com';
	# the 'From: ' field in emails
	$g_from_email			= 'noreply@curbstone.com';
	# the return address for bounced mail
	$g_return_path_email	= 'ira@curbstone.com';
	# allow email notification
	#  note that if this is disabled, sign-up and password reset messages will
	#  not be sent.
	$g_enable_email_notification	= ON;
	# --- sitewide variables ----------
	$g_window_title			= 'Mantis - i5 MySQL';	 # browser window title
	$g_page_title			= 'Mantis - i5 MySQL';	 # title at top of html page (empty by default, since there is a logo now)
	# FTP settings, used if $g_file_upload_method = FTP
        # fill in your unique address or URL
	$g_file_upload_ftp_server	= 'ftp.your_server.com';
	$g_file_upload_ftp_user		= 'readwriteuser';
	$g_file_upload_ftp_pass		= 'readwritepass';
	# Files that are allowed or not allowed.  Separate items by commas.
	# eg. 'php,html,java,exe,pl'
	# if $g_allowed_files is filled in NO other file types will be allowed.
	# $g_disallowed_files takes precedence over $g_allowed_files
	$g_allowed_files		= '';
	$g_disallowed_files		= 'php,pl,java';
	# --- cookie prefix ---------------
	# set this to a unique identifier.  No spaces.
	# should make this unique in case you have multiple implementations of Mantis from your same server
	$g_cookie_prefix		= 'MANTISi5';
  ?>






----
===== [[DB2i5|Using native i5 DB2/400]] =====

Issues of concern 2007-02-06

==== A. install.php user error ====

=== NOTE: /www/zendcore/htdocs/md/config_inc.php should NOT exist prior to running install.php. ===

/www/zendcore/htdocs/md/admin/install.php will not work.  When run with the following parms (fields only in order):

db2 (experimental)\\ 
## system database name from DSPRDBDIRE AS/400 command "Relational database" field\\ 
S1071FDD\\ 
PHP_IRA\\ 
p0okmju7yg\\     
## I tested with incrementing numbers for xxx, got up to 024, use a new one each time you run install.php\\ 
MANTISxxx\\ 
## BOTH pw's are required, you cannot leave user blank\\ 
PHP_IRA\\ 
p0okmju7yg\\ 
## box below is unchecked\\ 
[ ] Print SQL...\\ 
## then click\\ 
[Install...]\\ 

The LIBRARY *IS* being created with the database collection and journal, but the tables and indexes are not.  The database is created by the admin but the tables are made by the user.  The diff in the code for the two operations is:
 
FROM: /md/admin/install.php
TO CREATE DB which works
   $g_db = ADONewConnection( $f_db_type );
   $t_result = $g_db->Connect( $f_hostname, $f_admin_username, $f_admin_password );

The 9th green message at the top of the screen shows that the "Attempting to connect to database as ADMIN" was GOOD, so that worked.  
 
TO CREATE TABLES that does not
    $g_db = ADONewConnection($f_db_type);
    $t_result = @$g_db->Connect($f_hostname, $f_db_username, $f_db_password, $f_database_name);
 
The f_db_type is db2 for both.  Then I tried using the SAME id and pw:
    $t_result = @$g_db->Connect($f_hostname, $f_admin_username, $f_admin_password, $f_database_name);

And that does not work either.  Then I tried
    $t_result = @$g_db->Connect($f_hostname, $f_admin_username, $f_admin_password);

And that does not work either.  A debug statement I inserted shows: 
    Database user doesn't have access to the database ( ) HOST[S1071FDD] ID[PHP_IRA] PW[123123123] DB[MANTIS400] DBTYPE[db2]
 
Makes no sense as the [] parms are correct.  This does NOT appear to be an MD5 issue at all. (pw masked with 123123123)  I removed the @ sign from the connect() and got no additional error info.  In fact, the parens on the output screen are empty.
 

==== B. MD5 missing ====

Enabled mcrypt/mhash in the PHP extensions, and bounced.  That did NOT eliminate the MD5 warning.  
  "Checking for MD5 Crypt() support POSSIBLE PROBLEM - password security may be lower than expected".  
I had this with the MySQL install also!


==== C. db2_tables error ====

Install.php ERROR:\\ 
SYSTEM WARNING: db2_tables() expects parameter 1 to be resource, boolean given
 
comes from FILE:\\ 
/www/zendcore/htdocs/md/core/adodb/drivers/adodb-db2.inc.php and is complaining that the value of this->connection is null.

  function &MetaTables($ttype=false)
  {
  global $ADODB_FETCH_MODE;
  $savem = $ADODB_FETCH_MODE;
  $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
  $qid = db2_tables($this->_connectionID);

I cannot figure this out and do not know if it affects issue A.

==== D. Creating SQL database manually ====

1. Run the install.php to generate the SQL by checking the box.  Copy and paste into a PC file editor.

2. Removed formatting spaces to compact, as the SQL parser on the AS/400 has a length limit.  I try to keep the lines below 88 bytes.

3. Split any lines that exceeded 88.  There are just a few.

4. Add the required qualifier to tell DB2/400 WHERE to put the tables.  Prefix all table and index names with xxxx/ where xxxx is the library name that you designate in the "Database name" field in the install.php input screen.  "CREATE TABLE mantis_user_table" becomes "CREATE TABLE mantis011/mantis_user_table", for instance.

5. Fix the error in the SQL generated:


			365 CREATE TABLE mantis011/mantis_user_table (
			...
			371 date_created TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
			372 last_visit TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
			...
			380  PRIMARY KEY (id)
			381 );
			ERROR:  Timestamp does not match INSERT\\ 
			395 INSERT INTO mantis_user_table(username, realname, email, password, date_created,
			...
			399 '2007-02-06, 09:10:36PM', '2007-02-06, 09:10:36PM', 1, 0, 90, 3, 0, 0,
			400 'b4f45b80260a3ce540d14a897560ab9eed40d29997c90d4c63cda6ef153afb52');

THIS:\\ 
  399 '2007-02-06, 09:10:36PM', '2007-02-06, 09:10:36PM', 1, 0, 90, 3, 0, 0,
MUST BE:\\ 
  399 '2007-02-06 09:10:36', '2007-02-06 09:10:36', 1, 0, 90, 3, 0, 0,

6. Save the file as MANTISSQL on the PC

7. Create a Source Physical File on the AS/400


			                     Create Source Physical File (CRTSRCPF)
			 Type choices, press Enter.
			 File . . . . . . . . . . . . . . > MANTISSQL     Name
			   Library  . . . . . . . . . . . >   MANTIS011   Name, *CURLIB
			 Record length  . . . . . . . . . > 300           Number
			 Member, if desired . . . . . . .   *NONE         Name, *NONE, *FILE
			 Text 'description' . . . . . . .   Mantis SQL installation script

  CRTSRCPF FILE(MANTIS011/MANTISSQL) RCDLEN(300)TEXT('Mantis SQL installation script')

8. Add the working LIBRARY to your library list:\\ 
  addlible  mantis011

9. FTP the mantissql pc file tp MANTIS011/mantissql  SRCPF

  230 ICHANDLER logged on.
  ftp> cd mantis011
  250 "MANTIS011" is current library.
  ftp> lcd md
  Local directory now W:\LIVE\mantis-sql\md.
  ftp> pwd
  257 "MANTIS011" is current library.
  ftp> lcd
  Local directory now W:\LIVE\mantis-sql.
  ftp> lcd md
  Local directory now W:\LIVE\mantis-sql\md.
  ftp> put mantissql
  200 PORT subcommand request successful.
  150 Sending file to member MANTISSQL in file MANTISSQL in library MANTIS011.
  226 File transfer completed successfully.
  ftp: 17753 bytes sent in 0.00Seconds 17753000.00Kbytes/sec.

10. Once the file is in the source file on the AS/400, execute it by the Run SQL Statement command, shown prompted with F4:

			                        Run SQL Statements (RUNSQLSTM)
			Type choices, press Enter.
			Source file  . . . . . . . . . . > MANTISSQL     Name
			  Library  . . . . . . . . . . . >   MANTIS011   Name, *LIBL
			Source member  . . . . . . . . . > MANTISSQL     Name
			Commitment control . . . . . . . > *ALL          *CHG, *UR,
			Naming . . . . . . . . . . . . .   *SYS          *SYS, *SQL

And below in command line format:

  RUNSQLSTM SRCFILE(MANTIS011/MANTISSQL) SRCMBR(MANTISSQL) COMMIT(*ALL)

11. Congratulations.